CODEWITHSUNDEEP
sqlsql-serverstringdate
marilyn
marilyn

Reputation: 565

SQL Converting string MMM.YY to date

how do i convert/cast a column contains strings e.g. Jan.08,Feb.08.. into date format so that i can sort them?

Greatest Thanks!

Upvotes: 7

Views: 14055

Answers (2)

Jonathan
Jonathan

Reputation: 26639

If you can make the assumption that all dates will be within the last ten years, you can use the following code:

select convert(datetime, replace('Jan.08', '.', ' 20'))
select convert(datetime, replace('Dec.08', '.', ' 20'))

That formats the string into the format "Jan 2008", which is unambiguous. "Dec.08" could be "8th December this year" or "The month of december 2008".

Or you could use Matt Gibson's suggestion of prepending a "1." to your date before conversion. That removes the ambiguity, and has the advantage of using whatever defaults that SQL server has for dates (i.e. 50 is 1950 and 49 is 2049).

select convert(datetime, '1.' + 'Jan.08')
select convert(datetime, '1.' + 'Dec.49')
select convert(datetime, '1.' + 'Jan.50')

Upvotes: 0

Matt Gibson
Matt Gibson

Reputation: 38238

I'd just format as a convertible string for the first of the relevant month, and then cast to datetime, e.g.

CAST('1.' + YourMonthAndYearColumnName AS DATETIME)

...is an expression that will yield a datetime that should be sortable, so:

SELECT
  YourMonthAndYearColumnName
FROM
  YourTable
ORDER BY
  CAST('1.' + YourMonthAndYearColumnName AS DATETIME)

...should do what you're looking for.

Upvotes: 12

Related Questions