We've received some interesting feedback to Itzik's series about datetime calculations. Remember that you can share your thoughts about any of our articles in the SQL Server forums (http://sqlforums.windowsitpro.com), and you can leave comments specifically about Itzik's columns in the T-SQL subforum (http://sqlforums.windowsitpro.com/web/forum/categories.aspx?catid=80&entercat=y). We thought we'd share a couple of responses that Itzik has received from readers.
Reader Mike Smith shared the following quick technique:
Inspired by your article "DATETIME Calculations, Part 1" (InstantDoc ID 94487), I thought binary manipulation of a datetime value would offer the best performance when trimming the date or time part. I was unable to find a way (e.g., bitwise operators, reverse function) to grab the date portion, but I did discover this statement that keeps the time portion, and which I believe would offer fantastic performance (and it's simple!):
select cast(cast(@dt as varbinary(4)) as datetime);
Itzik responds, "That's a great technique for extracting only the time! You could use similar logic to extract only the date:
select cast(substring(cast(getdate() as binary(8)), 1, 4) + 0x00000000 as datetime)
but that requires further manipulation, making it a bit cumbersome."
Reader J. Ashley Bryan also shared a simple, intuitive technique:
In your article, you review three techniques for trimming the date value out of a datetime datatype. There is a fourth technique that I believe warrants consideration. You start by casting the datetime value to a float. The date and time are now represented as a decimal value, with the day to the left of the decimal and the time to the right. You then apply the Floor function against the float value to get the first whole number less than the datetime decimal value. With the time portion of the datetime value zeroed out, you then cast the float value back to datetime. The result is the midnight datetime entry for the value submitted.
select cast(floor(cast(getdate() as float)) as datetime);
Using the same performance test you used, I added this technique to compare the results. In a five-run comparison, the float technique had performance comparable to that of your recommended third technique and posted slightly better results than that technique overall. I used the results of your worst-performing technique (the first one) as the baseline for the comparative percentages, as you see in Table A. The float technique is a bit more intuitive and readable than the others, while maintaining good performance results. In addition, this technique can be used in other areas to help perform date-range operations by using the Ceiling function to find the next highest whole number from the value submitted.
...where dbdate >= cast(floor(cast(getdate() as float)) as datetime) and dbdate < cast(ceiling(cast (getdate() as float)) as datetime);
Itzik responds, "I'm usually reluctant to use floats because of their imprecise nature, particularly in calculations that require complete accuracy. However, I tested the technique with both midnight values and last possible accuracy unit (i.e., 23:59:59.999), and it seemed to perform well. Thanks for sharing!"
