Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Date Only and Time Only data types in SQL Server 2005 (without the CLR)

by Jeff Smith in  , ,

In this post, I showed a simple way to create simple but useful Date and Time user-defined data types in SQL Server 2000.  Here's how to do it in SQL Server 2005, without the need for CLR types or anything fancy.

First, we must create two user defined data types:

create type Date from dateTime
create type Time from dateTime

So, internally (and externally to our clients), these types are really just DateTime.  But, we will apply some rules to these types so that the Date data type will always be constrained to a time exactly at midnight, and the the Time data type will always be at the "base date", or 1900-01-01:

create rule DateOnlyRule as
  dateAdd(dd,datediff(dd,0,@DateTime),0) = @DateTime

go

create rule TimeOnlyRule as
  datediff(dd,0,@DateTime) = 0
 
go

Finally, we call sp_bindrule to bind the rules to the data types:

EXEC sp_bindrule 'DateOnlyRule', 'Date'
EXEC sp_bindrule 'TimeOnlyRule', 'Time'

That's it!  Now, we can create a table that uses our brand-new data types:

create table Trans
(
 TranID int identity primary key,
 TranAmount money not null,
 TranDate Date not null,
 TranTime Time not null
)
 
Notice that TranDate and TranTime are two separate columns here.   If we try to insert data, our rules will ensure that our TranDate and TranTime columns only contain the appropriate data, and by doing so we can simply calculate TranDate + TranTime to get the transactions DateTime.

The following inserts will succeed:

insert into Trans (TranAmount, TranDate, TranTime)
values (200, '2005-01-01', '09:00:00 AM')

insert into Trans (TranAmount, TranDate, TranTime)
values (400, '2005-01-03', '7:50:30 PM')

But this will fail, since our rules are violated (getdate() returns the current date and the time):

insert into Trans (TranAmount, TranDate, TranTime)
values (200, getdate(), getdate())

Msg 513, Level 16, State 0, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'PlayGround', table 'dbo.Trans', column 'TranDate'.
The statement has been terminated.

Finally, we can select from our table and simply add the two columns together to get each transaction's DateTime:

select *, TranDate + TranTime as TranDateTime
from Trans

TranID  TranAmount   TranDate            TranTime            TranDateTime
------- -----------  ------------------- ------------------- -------------------
1       200.00       2005-01-01 00:00:00 1900-01-01 09:00:00 2005-01-01 09:00:00
2       400.00       2005-01-03 00:00:00 1900-01-01 19:50:30 2005-01-03 19:50:30

(2 row(s) affected)

The nice advantage of doing this is that you can quickly and efficiently select all transactions for a single day like this:

select * from Trans
where TranDate = '2005-01-01'

If we stored the Date and Time in the same column, we'd need to use a less efficient range:

select * from Trans
where TranDate >= '2005-01-01' and TranDate < '2005-01-02'

SQL 2008 does finally implement true Date and Time data types, but until then, I hope this gives you some ideas to play with.

Notes:
  • If you do wish to break apart DateTime data into two columns, another option is to simply use a single, standard DateTime column but then define two extra computed columns in your table, one that returns just the Date, another that returns just the Time.  Then, you can index those computed columns and reference them easily and efficiently.  If you truly wish to store just a date or just a time, however, these user defined data types are a great way to do it.  At the end of this SQLTeam article there's a good example demonstrating this.

  • You could also just use the expressions for the rules in CHECK constrains on individual table columns, instead of creating the user defined data types and rules.  As Books On Line states, CREATE RULE will at some point be removed from future versions of SQL Server, so you may wish to avoid using it. 
see also:

Legacy Comments
Mladen
2007-10-31		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
no jeff, no!!
don't go tho the dark side :)))
Jeff
2007-10-31		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
I thought the "dark side" was CLR integration?
pootle_flump
2007-10-31		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
Hi Jeff

I think the SQL Server UDTs are pretty tame unless you use rules. However (from BoL 2005):

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CHECK constraints instead. For more information, see Constraints.


A shame because UDTs could be pretty powerful otherwise. This entry is a little stronger than the 2000 entry. I don't know what BoL 2008 has to say on the matter.
Mladen
2007-10-31		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
> I thought the "dark side" was CLR integration?

well not anymore... katmai will have at least 2 native CLR datatype that i can remember.
Scott Whigham
2007-11-01		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
Interesting approach - one that I never would have thought of today since rules have been deprecated for so long. I like it though!

I have to say that I wouldn't use UDTs even if they weren't being deprecated because they are impossible to use in a production environment. Once you define a UDT, you cannot under any circumstances change its definition; your only choice is to drop it and rebuild it. What effect would that have on your application? Several hundred thousand dollars worth of effect if I had to guess.
ahmed ibrahim
2007-12-23		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
this article is very helpful
Aneesh
2008-03-20		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
Hi,

I would like to know if i can get only the date part from a datetime value from database.
for ex: for 1/25/2008 i would like to get only '1'.
Shena
2008-05-04		 time computation
Hi!

Can you pls. help me?

i have a problem in time computation....
this is the scene...

account_name Time_IN Time_Out
shena 05-01-2008 17:00 05-02-2008 8:45

how can i compute the time if i only do the work from 17:00 to 18:00 of 05-01 and 8:00 to 8:45 of 05-02

thanks....
vibhanshu sahu
2008-09-29		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
i want to separate date and time from a single column, how can i do this.?
Autosurf Restarter
2008-11-18		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
Thanks for the good post. Interesting way of getting the time value.

Hopefully SQL 2008 will have an easy way to store Time values.
murthy
2009-01-10		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
Little bit confused...
vikram malhotra
2009-01-22		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
sir i want to insert this from parameter

param = cmd1.Parameters.Add("@totaloutput", SqlDbType.Time1:Datetime);

param.Value =Convert.Time1:Datetime(Txtworktime.Text);

this is used to insert from user form in asp.net
i have create the function in c#
Jeff S
2009-01-25		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
vikram -- I have no idea what that "Time1:DateTime" is ... my advice: consult a C#/asp.net programming book or website and learn the basics.
kundan
2009-11-13		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
The data type is working but when i m inserting value through .net 3.5 it shows error : incorrect syntax near '12'
Tom
2009-12-13		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)
Do the world a favor!!
Dont ever try to be a teacher!!!
Shakti Khurana
2010-02-09		 re: Date Only and Time Only data types in SQL Server 2005 (without the CLR)

WHERE [Timestamp] >=dateadd(day,datediff(day,0,GetDate())- 3,0) and DATEPART(HOUR, [TimeStamp]) = 02

-- [Timestamp] between (getdate() - 1) and getdate()

order by [Timestamp] desc

------- this clouse will give you last 2 days data with time if you want to compare on the basis of time use Datepart
------- whrere timestamp is your col name