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:
SQL,
TSQL,
datetime,
code