Getting the current date in SQL Server? [duplicate]

Question

How can I get the current date in MS-SQL Server 2008 R2?

The format of the column in my database is DATETIME and dates are stored in the following format:

+++++++++++++ Vrdate ++++++++++ |                             | |   2012-11-18 00:00:00.000   | |   2012-11-19 00:00:00.000   | |   2013-11-18 00:00:00.000   | |   2012-12-01 00:00:00.000   | |   2010-10-09 00:00:00.000   | |   2012-11-11 00:00:00.000   | |                             | +++++++++++++++++++++++++++++++ 

I searched but wasn't able to find the way to get the date in this format (i.e. which has the time associated with it in 00:00:00.00). I found GETDATE() function but that provides the current time along with the date as well, what I want is get the date in the following format: CurrentDate 00:00:00.00

How may I get this?

Answer 1

SELECT CAST(GETDATE() AS DATE) 

Returns the current date with the time part removed.

DATETIMEs are not "stored in the following format". They are stored in a binary format.

SELECT CAST(GETDATE() AS BINARY(8)) 

The display format in the question is independent of storage.

Formatting into a particular display format should be done by your application.

Answer 2

As you are using SQL Server 2008, go with Martin's answer.

If you find yourself needing to do it in SQL Server 2005 where you don't have access to the Date column type, I'd use:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) 

SQLFiddle