SQL Converting string MMM.YY to date

4
votes

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!

2
sqlsql-serverstringdate
It only depends on what database management system (DBMS) your are using. Oracle, mySQL, SQL Server ?... There is no standard SQL to do this. - Cyril Gandon
Is the string localized? Or always in certain language? - Stefan Steinegger
I added the sql-server tag, it may invite the sql-server experts :-) - Stefan Steinegger
hmm.. nope. it's not localized - marilyn
My suggestion would be to not store dates in this format anyway. I would suggest that you split this into 2 separate columns. Have one int column for month - That can be constrained to take a value 1-12 and another int column for year. This will allow you to more easily do things like find all records in the quarter April 2010 to June 2010. It will also more easily allow constraints to be added to prevent bad dates being inserted. - Martin Smith

2 Answers

7
votes

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.

0
votes

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