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!

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.

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