Getting the current date in SQL Server? [duplicate]

67

This question already has an answer here:

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?

Kamran Ahmed

출처 sql sql-server sql-server-2008 datetime current-time

2 답변

108
 

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.

  • I'd add that if OP wants to get a DATETIME result with TIME part set to '00:00:00.000', then he can use SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME). - LightBulb
  • @KamranAhmed, in that case, I'd like to point you to T-SQL Date and Time Function Reference. - LightBulb
  • Refresh and try again. I made a mistake and later changed it to a proper link. Sorry for that. - LightBulb
  • @LightBulb thanks, surely going to check that :) - Kamran Ahmed
14

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

Linked

Related

Latest