Get Date only from getdate() function

Question

Post reply

Get Date only from getdate() function

forum member

Ten Centuries

Points: 1290
More actions
March 30, 2010 at 8:58 pm

#231361

Hi
I am using sql server 2005 and i would like reterive date only from the GetDate(). Please let me know how can i do this
For example: 2010-03-25 13:55:43.547
the required is only should be 2010-03-25
Please suggest
Thanks

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 1

select convert(varchar,getdate(),111)

Regards,
Raj

http://Strictlysql.blogspot.com

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 2

As per required output -

select REPLACE( convert(varchar,getdate(),111), '/', '-')

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Dohsan SSCrazy Points: 2972 More actions · Answer 3

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS DT

If you do a quick search you should be able to find a few sites with lists of the different date formats using CONVERT.

Have a quick look over: http://www.sql-server-helper.com/tips/date-formats.aspx

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 4

Dohsan (3/31/2010)
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS DT
If you do a quick search you should be able to find a few sites with lists of the different date formats using CONVERT.
Have a quick look over: http://www.sql-server-helper.com/tips/date-formats.aspx

Thanks for this information.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Also asked here

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d12f8277-d921-4c92-9f03-a7019678cd7e

I answered

SELECT CAST(GETDATE() AS DATE)

N 56°04'39.16"
E 12°55'05.25"

brosspaxedi SSCommitted Points: 1591 More actions · Answer 6

If I'm not mistaken, SELECT CAST(GETDATE() AS DATE) will only work on SS2008 and above, as the DATE type is new in 2008.

*******************
What I lack in youth, I make up for in immaturity!

Paul White SSC Guru Points: 150467 More actions · Answer 7

If there are points available for being obscure...

SELECT {fn CURRENT_DATE()};

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Richellere SSC Eights! Points: 902 More actions · Answer 8

select cast(getdate() as date) doesn't work on SQL Server 2005

Paul White SSC Guru Points: 150467 More actions · Answer 9

richellere (4/3/2010)
select cast(getdate() as date) doesn't work on SQL Server 2005

Bross mentioned that. 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Nabha SSCrazy Eights Points: 8534 More actions · Answer 10

Paul White NZ (4/1/2010)
If there are points available for being obscure...
SELECT {fn CURRENT_DATE()};

:w00t: huh? :hehe: (ODBC yeah?)

---------------------------------------------------------------------------------

Carl Federl One Orange Chip Points: 25384 More actions · Answer 11

There is a very long set of posts titled "Just the date, please"

http://www.sqlservercentral.com/Forums/Topic379596-8-2.aspx

The below method is 10 times faster than converting to character and then truncating the time portion:

CAST(convert(int, convert(float, getdate() )) AS DATETIME)

Alternatively using all casts instead of converts:

CAST( CAST ( CAST getdate() as float ) as integer ) AS DATETIME)

SQL = Scarcely Qualifies as a Language

Paul White SSC Guru Points: 150467 More actions · Answer 12

Nabha (4/3/2010)
Paul White NZ (4/1/2010)
If there are points available for being obscure...
SELECT {fn CURRENT_DATE()};
:w00t: huh? :hehe: (ODBC yeah?)

Yeah - works in normal Transact-SQL too though!

Try it!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 13

Carl Federl (4/3/2010)
CAST(convert(int, convert(float, getdate() )) AS DATETIME)

Few of issues with this one.

1. Relies on undocumented internal implementation details.

2. Only works with DATETIME - none of the new 2008 types.

3. Cannot be persisted as a computed column (in versions before SQL Server 2008).

The DATEADD/DATEDIFF method is my preference FWIW.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 14

Paul White NZ (4/3/2010)
Carl Federl (4/3/2010)
CAST(convert(int, convert(float, getdate() )) AS DATETIME)
Few of issues with this one.
1. Relies on undocumented internal implementation details.
2. Only works with DATETIME - none of the new 2008 types.
3. Cannot be persisted as a computed column (in versions before SQL Server 2008).
The DATEADD/DATEDIFF method is my preference FWIW.

Don't forget what happens to dates with time portion between 12:00 and 24:00 (pm times)...

N 56°04'39.16"
E 12°55'05.25"