Get only Date part for SQL Server DateTime value

There may be various scenarios wherein you need only Date part of an SQL Server DateTime value, rather than full DateTime value.

This is very much a requirement in case of comparing dates (only date and no datetime).

SQL Server 2008 offers a new datatype DATE which holds only Date. 

So you can simply use Convert(Date, GetDate()) to convert your full DateTime into Date. Its as simple as that. 

But if you are using a version prior to SQL Server 2008, you need to do a workaround. There are lot many ways to achieve that. Following is one of them which is being used too frequently.

Say your DateTime value is "2012-04-20 21:45:00.938"

First convert date into a varchar in format such as (dd/mm/yyyy or mm/dd/yyyy) depending upon the regional settings of the SQL Server's host environment.

SELECT CONVERT(varchar(10), GETDATE(), 101) -- 101 stands for mm/dd/yyyy (mostly in case of US)

SELECT CONVERT(varchar(10), GETDATE(), 103) -- 103 stands for dd/mm/yyyy

So, it will result into - "04/20/2012", "20/04/2012" respectively

But this is a varchar type, so you need to convert it back to DateTime type to make it able to be used in Date time operations.

Use either of this depending upon the settings in SQL server's host environment.

SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101) )

or

SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 103) )

This will now result to "2012-04-20 00:00:00.000"