Q) How in SQL Server can I get a date value without any time value?

Question

How in SQL Server can I get a date value without any time value?

Answer

In SQL Server 2008 Microsoft introduced the DATE data type so you can simply do the following:

SELECT CAST(GETDATE() as DATE)

In SQL Server 2005 and below the DATETIME data type always retains a time component.  If you are not interested in time then commonly you set this to 00:00:000.  This will ensure sorting and aggregations on the “date” component are consistent.  A quick and easy way to convert a datetime to a standardized date is to do the following:

SELECT CAST(CONVERT(VARCHAR(50),GETDATE(),112) as DATETIME)

This has the benefit in that it results in a proper date time value (rather than a string) and is also deterministic so can be used in indexed views etc.

All Answers provided are subject to our standard Answers Disclaimer.