Tuesday, December 07, 2010

Using SQL Server Dates Without Time

I often find myself in a scenario where I want customers to be able to select dates, delete items by setting an inactive date to today rather than actually removing something from a database, etc...

Sometimes it helps to only deal in dates, rather than in date/time. Using the GetDate() function in SQL brings back a full date with the time string, but here is a great method to fix that.

declare @somedate = cast(floor(cast(GetDate() as float)) as DateTime)

There are a few other ways to do this, but this one seems to perform the best. Tested with SQL 2008 and works great.

Hat tip too Ben Nadal's blog, more info here-

http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm