Parse a date from unformatted text in SQL

Question

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?

Answer 1

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)

Answer 2

try:

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

output:

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

(1 row(s) affected)

Answer 3

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

Answer 4

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.