SQL server datetime datatype - seperating date and time
I've reprinted the article only so that I could do a search on my blog and find it. I am not printing this to take credit for it. It was written by the author at the url specified above.
---------------------------------------------------------
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 clase 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:
A) WHERE DateDiff(dd, DateTimeColumn, '1/1/2004') = 0
B) WHERE Convert(varchar(20), DateTimeColumn, 101) = '01/01/2004'
C) WHERE DateTimeColumn LIKE '1/1/2004%'
D) WHERE Year(DateTimeColumn) = 2004 AND Month(DateTimeColumn) = 1 and Day(DateTimeColumn)=1
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
2) Enforce that the Date column never has a time stored in it:
DateCol datetime check (dateAdd(dd,datediff(dd,0,DateCol),0) = DateCol)
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. You might wish to consider using the smalldatetime datatype to avoid this problem, if you can.
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.
posted by BofYonkers at 11:58 AM
<< Home