sql server - 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

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