My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.

I don't like storing dates along with the time portion in my database tables.

Sure, I use the datatype, but I wish SQL provided a “Date” datatype and a “Time” datatype that were separate. It really sometimes requires too much work in T-SQL to separate the date portion from the time portion, and often in a WHERE clasue you don't care about the time, you just want data for a single day. A common question asked at SQLTeam is:

Why doesn't the condition WHERE DateTimeColumn = '1/1/2004' return any results? I can see there is definitely data in that table for Jan 1st, 2004 !

The answer, of course, is because “DateTimeColumn” contains a time portion as well. Some solutions that are given often look like this:

I don't really like any of those, to be honest. Many rely on system settings for date formats. Others rely on conversions to varchars. None are able to make use of any indexes on the DateTimeColumn so they will not be as efficient as possible.

The best solution in terms of performance isn't even really that good, because it requires a range even though we wish to return data just for a particular day:

E) WHERE DateTimeColumn >= '1/1/2004' AND DateTimeColumn < '1/2/2004'

If you encounter this situation often, here's an idea:

Don't store the time with the date in your database! Keep them in separate columns. You can always combine them extremely easily just by adding them together to recreate your original DateTime value. But for all those other times when you wish to just filter for a particular day, you can use a quick and efficient = or BETWEEN comparison on the date only.

To do this, you need to :

1) Create two columns instead of one -- a Date column and a Time column

3) Enforce that the Time column never has a date portion that is a date other than the “base date” (which is the datetime that the numeric value 0 converts to):

TimeCol datetime check (datediff(dd,0,TimeCol) = 0))

Once that is done, any time you wish to return the Date along with the Time, you can just add them together! That's right, just SELECT DateCol + TimeCol as DateTimeVal FROM YourTable.

The key here is now you can index your DateCol and filter off of it directly, without worrying about conversions or time issues, which results in efficient execution plans. With the constraints, you are guaranteed that only dates are stored there, so you never have to worry about it. And, you can effortlessly retrieve the original DateTime value with a simple addition of the two columns (no conversions needed!).

If you like, you can create a View of your table and have that view return your DateTime column as well as the Date and the Time broken out if you like. You can even allow for this View to be updateable so that client applications don't have to worry about manually breaking out the Date and the Time themselves. See the code below for an example of this trigger, along with the constraints and some sample data.

-- Here's our sample table; just an ID column with a Date and a Time.

-- Note the CHECK constraints on the datetime columns:

create table Test_table

(ID int primary key,

TransDate datetime

check (dateAdd(dd,datediff(dd,0,TransDate),0) = TransDate),

TransTime datetime

check (datediff(dd,0,TransTime) = 0)

)

go

-- Here's our view of the table which presents a DateTime column

-- that looks “normal“, along with the date and the time broken out

create view Test_View as

select ID,

TransDate,

TransTime,

TransDate + TransTime as TransDateTime

from

Test_Table

go

-- Here's our trigger for when rows are added to the view;

-- you can do a similiar thing for an UPDATE trigger as well.

create trigger test_view_ins on Test_View

instead of insert

as

begin

insert into

Test_table (ID, TransDate, TransTime)

select

ID, JustDate, TransDateTime - JustDate

from

(

select ID, dateAdd(dd,datediff(dd,0,TransDateTime),0) as JustDate,

TransDateTime

from

inserted

) a

end

go

-- Insert some sample data using the View; the trigger

-- will break out the date from the time:

insert into Test_View (ID, TransDateTime)

select 1, getdate() union

select 2, '12/19/1972 10:00 AM' union

select 3, getDate() - 1.1 union

select 4, getdate() + 2.2

-- Let's see what we've got:

select * from Test_View

select * from test_Table

-- Clean it up:

go

drop trigger test_view_ins

drop view Test_View

drop table Test_Table

A couple of notes:

1) This does, of course, require more memory since you now require twice as much storage for your DateTime values.

2) If, on occasion, you do wish to SELECT from this table and filter by a specific date and time range, then the view will not be especially efficient if you simply use:

WHERE DateTimeCol BETWEEN @StartDateTime AND @EndDateTime

This is because now DateTimeCol is now a formula. You can either make use of Indexed Views to index this column, or you can use what I call an efficiently redundant WHERE clause like this:

WHERE DateCol BETWEEN (@StartDateTime-1) AND (@EndDateTime+1) AND
DateTimeCol BETWEEN @StartDateTime AND @EndDateTime

That will allow SQL Server to use the index on the DateCol for the initial filter, and then from there, the second part of the WHERE clause will have many fewer rows to scan.

The DATE and TIME datatypes were removed from 2005 due to some implementation problems. As I understand it, the issue was that MS decided to implement them as CLR UDTs (behind the scenes) and had a bunch of issues making them fully compatible with the various datetime functions (DATEADD/DATEDIFF/etc), so they dropped them altogether from the spec. I'm sure you could still write your own UDTs to implement it, but I question the real value of such an implementation.

This is great! I've have now the date and time columns been seperated. Now my other challenge is how can I convert them (currenty GMT) into an EST? Any comments and or help is definitely appreciated.Thanks,manny