SQL Server DateTime Trick #1

What if you wanted to trim the Time Of Day out of the full SQL DateTime value.

One solution would be to first take the datetime you want to deal with, then convert a string of the combined datepart elements back into a DateTime, like so:

declare @DateFull datetime declare @DateNow datetime set @DateNow = GETDATE() set @DateFull = convert(char(4), datepart(yyyy, @DateNow)) +         right('0' + convert(varchar(2), datepart(mm, @DateNow)), 2) +         right('0' + convert(varchar(2), datepart(dd, @DateNow)), 2)          print @DateNow print @DateFull

Feb 15 2009 11:36PM Feb 15 2009 12:00AM

 

Works just fine, but that is an extraordinarily expensive operation. Surely those really smart SQL developers thought about this.

Then I stumbled upon this article: "Temporal Datatypes in SQL Server" (http://www.simple-talk.com/sql/t-sql-programming/temporal-datatypes-in-sql-server/)

Long story short…

print CAST(FLOOR(CAST(GetDate() as FLOAT)) as datetime)

Feb 15 2009 12:00AM

 

Not only is it easier to read but it will much more reliably give you the correct inclusive beginning of day (not just SQL 2008 either). Use CEILING instead of FLOOR to get the inclusive end of day.