As I’ve mentioned in other posts, a DateTime is stored using a numerical representation. This provides some interesting usages.
For example this means that in order to add days to a DateTime, one can simply add a number, like this: GetDate() + 5 which will add 5 days to the DateTime returned by GetDate(). Simple and easy.
You can however also convert a DateTime to a float, which provides some other “fun” possibilities.
If you convert a DateTime to a float, you’ll get a decimal number where the portion in front of the decimal point is the Date portion and the number after the decimal point is the Time portion (for 1900-01-01). For example:

DECLARE @DateTime DATETIME 
SET @DateTime = '20081224 23:50:10' 
SELECT CAST(@DateTime AS float)    

This returns 39804.9931712963
If I then execute:

SELECT CAST(39804 AS DATETIME)

I get 2008-12-24 00:00:00.000 meaning the date representation of the DateTime. Subsequently doing it for the remainder portion (0.9931712963) I get 1900-01-01 23:50:10.000
This then means that to get a DateTime cleaned for the time portion all I need to do is:

SELECT CAST(FLOOR(CAST(@DateTime AS FLOAT)) AS DATETIME)    

Then I get the Date 2008-12-24 00:00:00.000.
While “fun”, SQL Server 2005 already has a built in conversion so the above will provide you with the same result as doing:

SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateTime,105),105)
(the syntax can be read in the MSDN documentation)

I’ve found no difference in the performance between the two when running them, so my thoughts are that SQL Server optimizes them both to identical code to execute.
So which is preferred I guess is a matter of preference and personal opinion.
Generally – I like the “Float” method cause it seems really clean, but it is perhaps more difficult to read and thus maintain further down the line for yourself or others, if one does not know the workings of it.
Code can be too clever as well.

Lately I’ve been reading some contradicting information about SQL indexes.
This time it was that DateTime fields in a database was prime candidates for usage as index keys, and that DateTime wasn’t prime candidates.

The reasoning behind the first statement looks to be that if you have a date and/or time stamp that you use in your data, you often filter on said data, thus making it advisable to use as index.
The reasoning behind the contrary statement that DateTime data is not advisable to use as index keys is that because a DateTime is stored with time down to milliseconds, and it is rare that you actually need that type of precesion in your queries, you will induce index scans more often by having the DateTime in your index.

Color me confused and call me Barney. So I decided to try and gain an understanding of these two contradicting statements to see whether they both were correct. Not that I think I can prove anything, but hey, I need to satisfy my own curiosity a bit.

So I decided to make a table:

And filled it with testdata.

DECLARE @i INT
SET @i = 0
DECLARE @d DateTime
DECLARE @d2 DateTime
WHILE @i < 10000 BEGIN
 SET @d = GETDATE()
 SET @d = DATEADD(dd, @i % 4, @d)
 SET @d2 = CONVERT(VARCHAR(16), @d, 112)
 INSERT INTO DateTimeTest
 VALUES
 ('abcdefg' + STR(@i) + 'h' + STR(@i % 4), @d, @d2)
END   

You’ll notice that I have two fields with DateTime (one and two), and this is because I wanted to clean one of the dates for its time representation to see if that made any difference. (Basically, it didn’t :))

I then made 2 indexes (unique, non-clustered). IX1 was made containing DateTimeOne and id, and IX2 was made containing DateTimeTwo and id (remember, DateTimeTwo contains the same date as DateTimeOne, but the time representation is set to 00:00:000)

I then ran a series of queries to see what result I would get in the execution plan.
For example:

DECLARE @d DateTime
SET @d = '2008-01-29'
SELECT id
FROM  DateTimeTest
WHERE DateTimeOne >= @d

SELECT id
FROM  DateTimeTest
WHERE DateTimeTwo >= @d   

This provides the same result for both queries, both seeking the index. I tried the same when selecting the DateTimeOne and DateTimeTwo values out in their respective queries and got the same result. 2 times Index seeks, and both queries with the same numbers in the execution plan.
 
So this had me starting to believe that the first case from the starting conundrum is true. However...what if you only needed rows from a specific day or similar information?
 
So I ran some queries along this type:

DECLARE @d DateTime
SET @d = DATEADD(dd,2, GETDATE())
SELECT id
FROM  DateTimeTest
WHERE DATEPART(dd, DateTimeOne) < DATEPART(dd, @d)

SELECT id
FROM  DateTimeTest
WHERE DATEPART(dd, DateTimeTwo) < DATEPART(dd, @d)   

And lo and behold – I got my elusive index scans. Naturally I would say, because now I could not use the indexes as they were build, but had to compute on them, making them suboptimal.

So it looks that there are truths in both statements, and it depends on how you use your data, but that is basically true for any index. To make the optimal indexes, one should always analyze the data.

Now that I was in the middle of all this testing, I started to wonder that since the internal representation of a datetime field is 2x4 bytes of integer, then – could that be used to speed up the queries.
So I made a third date field, but in this I placed the value of DateTimeOne cast as an integer.
I then ran the queries:

DECLARE @d DateTime
SET @d = DATEADD(dd,2, GETDATE())
SELECT id
FROM  DateTimeTest
WHERE DateTimeOne < @d

SELECT id
FROM  DateTimeTest
WHERE DateTimeOneInt < CAST(@d AS integer)   

To my surprise, this performed equally well for both queries, both showing the same numbers in the execution plan. It wasn’t until I had to select the DateTimeOneInt as a DateTime and thus had to convert in my SELECT statement that a small penalty was incurred. I also found that I did infact not really need to cast @d to an integer. I could just use it in the WHERE statement directly.

 

Occasionally I have the problem that I need to run as query with a form of dynamic sorting - meaning sometimes ascending or descending and sometimes based on an ordernumber or a date, for example.

Now a CASE statement in the order by is often very effective for handeling such issues, but I've not found it possible to combine it with the Ascending and Descending clasuses (yet, could be that I'm just doing it wrong). 

For example suppose we have a table which contains a "Date" field of the type DateTime, and we need to be able to sort this both descending and ascending based on some input parameter. Instead of making two queries, one might think that something like this would work

DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY DATE
    CASE @ASC
        WHEN 1 THEN ASC
        ELSE DESC
    END

But well - it doesn't: "Incorrect syntax near the keyword 'CASE'."

Or one might think:

DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY
    CASE @ASC
        WHEN 1 THEN Date ASC
        ELSE Date DESC
    END

Which gives a nice little: Incorrect syntax near the keyword 'ASC'

A little fix to get around this would be to not utilize case, but instead make the Ascending and Descending a factor so when Ascending you have a factor of 1 and Descending you have a factor of -1. Then simply convert/cast the Date to an integer (which should not be a terrible expensive procedure, as the datetime is represented as a number anyway, I wonder if it is possible to somehow use the internal representation for my purpose, or if the database does that when casting as integer....oh, well), and multiply the factor to the order by.

This mean the following two queries give the same result:

SELECT  *
FROM DateTimeOrderTest
ORDER BY CAST(Date AS INT) * -1

SELECT *
FROM DateTimeOrderTest
ORDER BY Date DESC

This technique of multiplying either 1 or -1, can be used in many queries, and of course is not limited to datetimes.

Anyways, I've found it quite useful myself.