convert month name in varchar to date so as to ord

问题:

This question already has an answer here:

• Convert month name to month number in SQL Server 13 answers

A table exists which stores month name in the form of string.. I want to order by year and month so as to get proper result.

year(string)  Month(string)  data
------------  ------------   ----
2012           August        bigbox
2012           December      samllbox
2013           April         samll box
2012           September     larg

I want to order by year and month. as in 2012,2013... Jan,feb,march....

I am aware of the method of case statement when Month = 'january' THEN 1 Month - FEB THEN 2

But i do'nt want to use this as the procedure will be too big..

回答1:

SELECT * 
FROM TABLE
ORDER BY [year] DESC,
         DATEPART(month,[Month] + ' 01 ' + CONVERT(VARCHAR(4),[YEAR]))

The above code will give you what you want , but i would strongly suggest you reconsider your design.

Right now you are reserving a string type field which would be at least 15 characters long. This field does not have any value than for display reasons. You could have a DATETIME field that would be much easier to short by (not having to do calculations there) and if you would like to display the name of the month you could use:

DATENAME ( month, DateField )

回答2:

Your best option is to use the proper date type. Otherwise, create a table (inline or physical) to map your string months.

SELECT 1 AS month, 'January' AS strMonth
UNION ALL
SELECT 2, 'February'
UNION ALL
SELECT 3, 'March'
...
SELECT 12, 'December'

Then map this your table. See a demo