6

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!

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
marilyn
  • 545
  • 3
  • 12
  • 19
  • 2
    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 Jun 29 '10 at 08:36
  • Is the string localized? Or always in certain language? – Stefan Steinegger Jun 29 '10 at 08:41
  • I added the sql-server tag, it may invite the sql-server experts :-) – Stefan Steinegger Jun 29 '10 at 08:43
  • hmm.. nope. it's not localized – marilyn Jun 29 '10 at 08:51
  • 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 Jun 29 '10 at 13:58

2 Answers2

11

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.

Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
0

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')
Jonathan
  • 25,873
  • 13
  • 66
  • 85