views:

387

answers:

4

I'm trying to figure out an elegant way to get a date from a text column that has data like this "YYYYMMDD"...so we might see "20060508" as a value in the column, which I would like to be able to return from a query as a date (May 8, 2006).

I'm sure I can hack something together given enough time, but the approaches I'm thinking of seem pretty kludgy, and I suspect there's a way this can be elegantly done in a single query.

Any suggestions?

+8  A: 

This is already a valid date - ISO-8601 format - just use:

SELECT CAST('20060508' AS DATETIME)

or alternatively:

SELECT CONVERT(DATETIME, '20060508', 112)

and that should do just fine!

In order to get your "May 08, 2006" display, do another convert into varchar, using the date convert style 107:

SELECT CONVERT(VARCHAR(25), CAST('2006-05-08' AS DATETIME), 107)

See here for more information on casting & converting in MS SQL

marc_s
returns date as _2006-05-08 00:00:00.000_ and not _May 8, 2006_
KM
+1. I'd choose the CAST one myself.
RichardOD
yes, CAST is the preferred way to do it, but in order to get a specific style, you might need to use the CONVERT with a style ID at times.
marc_s
@KM: yes, I just noticed that the OP didn't want a DATETIME in the end, but a specific string representation; updated my answer
marc_s
Thanks! I knew that this could be simpler, but I didn't realize how much! Didn't occur to me that this would be a valid date format!
Beska
Actually, no...I was just giving the date/time string as a sample to just describe the date that I wanted to result from the input, in case it wasn't clear. As long as a date is returned, I'm good. Many thanks.
Beska
Added the link that Scott Ivey posted in his answer, since that explains the "magic" numbers in your answer.
Beska
+2  A: 
select cast('20060508' as datetime) AS MyDate

gives you this result...

MyDate
-----------------------
2006-05-08 00:00:00.000

See here for more information on casting & converting in MS SQL.

If you're trying to achieve a specific format (May 8, 2006) - you should consider just returning the column as a datetime value, and letting whatever is going to display that value to the end user (website, client app, report, etc) do the formatting. If you format it at the query, you'll be returning a string, which will make it harder to swap out formats from your front end in the future. If you want to do it in SQL - check out format 107 in the link above.

Scott Ivey
Thanks for the link....that explains some of the magic about the numbers I'm seeing in the other answers.
Beska
+1  A: 

No sweat, just CONVERT it. Style "112", or ISO would handle your example case.

SELECT CONVERT(datetime,'20060508',112)

returns

 ----------------------- 2006-05-08 00:00:00.000

 (1 row(s) affected)
Chris McCall
+1  A: 

try:

SELECT REPLACE(CONVERT(varchar(30),CONVERT(datetime, '20060508'),107),' 0',' ')

output:

-------------------
May 8, 2006

(1 row(s) affected)
KM
Thanks for the extra formatting, though it's not required...I was just giving that output to better describe the format that the data was starting in and how it should be correctly translated. As long as it's in date format, I can deal with it.
Beska