Getting current date in SQL Server?

Question

How to get current date in MS-SQL Server 2008 R2?

The format of the column in my database is DATETIME , and the 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 could not find a way to get the date in this format (i.e. the time associated with it at 00:00:00.00 ). I found the GETDATE() function, but it also provides the current time along with the date, and I want to get the date in the following format: CurrentDate 00:00:00.00

How can i get this?

Answer 1

 SELECT CAST(GETDATE() AS DATE) 

Returns the current date with the deleted time portion.

DATETIME not saved in the following format. They are stored in binary format.

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

The display format in the question is independent of the repository.

Formatting to a specific display format should be done by your application.

Answer 2

How do you use SQL Server 2008, go with Martin's answer.

If you need to do this in SQL Server 2005, where you do not have access to the Date column type, I would use:

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

SQLFiddle