SQL Converting string MMM.YY to date

Question

Hi,

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!

Answer 1

In Oracle, it is pretty simple :

SELECT *
FROM myTable
ORDER BY to_date(myDate,'MON.YY')

Unfortunately, some research says that SQL Server doesn't want you to easy convert String into date : http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion

So you must add some code in your model. Maybe a map that says '01'=> 'Jan', '02'=> 'Feb' and so on...

Then reorder your string, put the year on the beginning, and compare your string data with '0801' < '0802'.

Or you can do the ugliest query in the world :

SELECT *
FROM myTable
ORDER BY CASE 
         WHEN SUBSTRING(myStringDate,0,3) = 'Jan' THEN SUBSTRING(myStringDate,3,2) + '01'
         WHEN SUBSTRING(myStringDate,0,3) = 'Feb' THEN SUBSTRING(myStringDate,3,2) + '02'
etc...
       END

Not tested, but you have the idea =D

Answer 2

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.

Answer 3

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')