MikeHouston.net

Honorary member of the Shaolin

Simple TSQL datetime tricks...

Someone ask me about working with dates recently in TSQL...I was too busy at the time to stop and remember this...I did get back to them later ;-)

Here it is. I'm sure most people know this, but at least I want forget next time...

DECLARE @CurrentDateWithTime datetime
DECLARE @CurrentDateZeroTime datetime
DECLARE @TomorrowZeroTime datetime
 
SET @CurrentDateWithTime = GETDATE()
SET @CurrentDateZeroTime = CAST(FLOOR(CAST(@CurrentDateWithTime AS float)) AS datetime)
SET @TomorrowZeroTime = CAST(CEILING(CAST(@CurrentDateWithTime AS float)) AS datetime)
 
PRINT @CurrentDateWithTime
PRINT @CurrentDateZeroTime
PRINT @TomorrowZeroTime

Why does it work? Add the following PRINT statements and it becomes obvious.

PRINT CAST(@CurrentDateWithTime AS float)
PRINT CAST(@CurrentDateZeroTime AS float)
PRINT CAST(@TomorrowZeroTime AS float)

 

The output should be :

Mar 26 2008  6:22PM
Mar 26 2008 12:00AM
Mar 27 2008 12:00AM
39531.8
39531
39532

 

Technorati Tags: ,,,
Comments are closed