Date Only from Date and Time

Question

Date Only from Date and Time

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
June 13, 2012 at 9:12 am

#262853

HI Everyone
I have something simple that I am getting stumped on.
I have a complete date with time (2012-06-12 10:43:37), and I need only the date portion. I have used DATEPART() to separate the Year, Month and Day. But I am having difficulty putting those values back together so that I come up with a correct data type of datetime (2012-06-12 00:00:00), or even just the date (2012-06-12)
I am close, but I am coming up with a date that is not correct.
Thank you in advance for your assistance
Andrew SQLDBA

Viewing 13 posts - 1 through 12 (of 12 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

AndrewSQLDBA (6/13/2012)
HI Everyone
I have something simple that I am getting stumped on.
I have a complete date with time (2012-06-12 10:43:37), and I need only the date portion. I have used DATEPART() to separate the Year, Month and Day. But I am having difficulty putting those values back together so that I come up with a correct data type of datetime (2012-06-12 00:00:00), or even just the date (2012-06-12)
I am close, but I am coming up with a date that is not correct.
Thank you in advance for your assistance
Andrew SQLDBA

SELECT CAST(yourdatetimecolumn AS DATE)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Thank You

I was trying to go about that the long way around of taking each part and concatenating it back together and then cast it to the correct data type.

Thanks for your help

Andrew SQLDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

ChrisM@Work (6/13/2012)
AndrewSQLDBA (6/13/2012)
HI Everyone
I have something simple that I am getting stumped on.
I have a complete date with time (2012-06-12 10:43:37), and I need only the date portion. I have used DATEPART() to separate the Year, Month and Day. But I am having difficulty putting those values back together so that I come up with a correct data type of datetime (2012-06-12 00:00:00), or even just the date (2012-06-12)
I am close, but I am coming up with a date that is not correct.
Thank you in advance for your assistance
Andrew SQLDBA
SELECT CAST(yourdatetimecolumn AS DATE)

And for those reading this and not getting this to work because you are still running SQL Server 2005 (or earlier):

select dateadd(dd,datediff(dd,0,yourdatecolumn),0)

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 4

SET DATEFORMAT dmy;

GO

SELECT

[Getdate] = GETDATE()

,[Year] = DATEPART ( yy , GETDATE() )

,[Month] = DATEPART ( mm , GETDATE() )

,[Day] = DATEPART ( dd , GETDATE() )

,[and back again] = CAST ( CAST(DATEPART ( dd , GETDATE() ) AS VARCHAR(4))+'.'+ CAST (DATEPART ( mm , GETDATE() )AS VARCHAR(2))+'.'+CAST (DATEPART ( yy , GETDATE() )AS varCHAR(4)) AS DATETIME)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

440692 I am just a number (6/13/2012)
SET DATEFORMAT dmy; GO SELECT [Getdate] = GETDATE() ,[Year] = DATEPART ( yy , GETDATE() ) ,[Month] = DATEPART ( mm , GETDATE() ) ,[Day] = DATEPART ( dd , GETDATE() ) ,[and back again] = CAST ( CAST(DATEPART ( dd , GETDATE() ) AS VARCHAR(4))+'.'+ CAST (DATEPART ( mm , GETDATE() )AS VARCHAR(2))+'.'+CAST (DATEPART ( yy , GETDATE() )AS varCHAR(4)) AS DATETIME)

A lot of unnecessary work for what can be doe using either of the following:

SELECT CAST(yourdatetimecolumn AS DATE)

select dateadd(dd,datediff(dd,0,yourdatecolumn),0)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Lynn Pettis (6/13/2012)
ChrisM@Work (6/13/2012)
AndrewSQLDBA (6/13/2012)
HI Everyone
I have something simple that I am getting stumped on.
I have a complete date with time (2012-06-12 10:43:37), and I need only the date portion. I have used DATEPART() to separate the Year, Month and Day. But I am having difficulty putting those values back together so that I come up with a correct data type of datetime (2012-06-12 00:00:00), or even just the date (2012-06-12)
I am close, but I am coming up with a date that is not correct.
Thank you in advance for your assistance
Andrew SQLDBA
SELECT CAST(yourdatetimecolumn AS DATE)
And for those reading this and not getting this to work because you are still running SQL Server 2005 (or earlier):
select dateadd(dd,datediff(dd,0,yourdatecolumn),0)

Whether you're stuck in a timewarp or not, it's well worth spending some time on select dateadd(dd,datediff(dd,0,yourdatecolumn),0) to properly understand it. It's a tremendously useful piece of code.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jeffem Ten Centuries Points: 1252 More actions · Answer 7

Though the answers have already been shared, it can be useful to have the options for CONVERT() available at your disposal. This link[/url] can be a useful primer...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

jeffem (6/13/2012)
Though the answers have already been shared, it can be useful to have the options for CONVERT() available at your disposal. This link[/url] can be a useful primer...

Only if you're wearing your sunnies. It's ghastly!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jeffem Ten Centuries Points: 1252 More actions · Answer 9

ChrisM@Work (6/13/2012)
jeffem (6/13/2012)
Though the answers have already been shared, it can be useful to have the options for CONVERT() available at your disposal. This link[/url] can be a useful primer...
Only if you're wearing your sunnies. It's ghastly!

Isn't it?! It's like somebody got a copy of HTML For Dummies from 1996.

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 10

@Lynn

I absolutely agree,

but (there had to be one didn't there 🙂 )

The OP did ask about how to concatenating the values back

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

440692 I am just a number (6/13/2012)
@Lynn
I absolutely agree,
but (there had to be one didn't there 🙂 )
The OP did ask about how to concatenating the values back

And we showed a better way rather than breaking apart and concatenating, which is much slower.

WayneS SSC Guru Points: 95460 More actions · Answer 12

... Check out Lynn's date/time conversions blog post. The link is in my signature block below.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2