convert month name in varchar to date so as to order by month

Question

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..

You should have thought on that before selecting `string` as your data type. You can look for a function in your DBMS and see how that could be handled. — Mosty Mostacho, Oct 11 '13 at 13:53
If you can change the layout of your table, you should really use a datetime column, it's so much easier to use (ORDER BY datetime DESC) is all you need. — Kevin, Oct 11 '13 at 13:54

Kermit · Answer 1 · 2013-10-11T14:01:38.873

3

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

edited Oct 11 '13 at 14:01

answered Oct 11 '13 at 13:54

Kermit

33,827
13
85
121

thanks but my procedure will be full of logics.. i wanted some in built function... – user2637506 Oct 11 '13 at 14:06
3

A built in function is not the solution when there is poor design. – Kermit Oct 11 '13 at 14:06

Giannis Paraskevopoulos · Accepted Answer · 2013-10-11T16:49:30.587

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 )

edited Oct 11 '13 at 16:49

answered Oct 11 '13 at 13:54

Giannis Paraskevopoulos

18,261
1
49
69

4

Why are you assuming SQL Server? – Mosty Mostacho Oct 11 '13 at 13:54
I am working on sql server 2008. And calso can you explain me this.. Bcz i get the result sorted by month name properly but its like {2013 January 2013 January 2012 January} – user2637506 Oct 11 '13 at 14:03
i want the answer like..2013 jan, 2013 april, 2013 august, 2012 jan , 2012 march.... – user2637506 Oct 11 '13 at 14:05
1

@MostyMostacho You are right. I just guessed, though i should ask first. – Giannis Paraskevopoulos Oct 11 '13 at 14:06
@user2637506 My new edit will order by year desc, month asc. – Giannis Paraskevopoulos Oct 11 '13 at 14:07
@user2637506 That's not how databases work. You should fix the root problem. – Kermit Oct 11 '13 at 14:08
Thanks this was perfect... i did guess this.. but i had stored year in string format so was wondering will this do the job.. Any ways thank you veyr much for the quick help.. :-) – user2637506 Oct 11 '13 at 14:11
3

We meet again. :-) Please don't use shorthand like `MM`. To understand why, try `SELECT DATEPART(W,GETDATE()), DATEPART(Y,GETDATE());`. If you mean `MONTH`, just type `MONTH`. – Aaron Bertrand Oct 11 '13 at 14:53
@AaronBertrand Hehe, i had read that article of yours, and that is why i used `month` in the second example. The first one just slipped. – Giannis Paraskevopoulos Oct 11 '13 at 16:32
But you're still using `VARCHAR` without a length. :-( – Aaron Bertrand Oct 11 '13 at 16:48
@AaronBertrand Darn.....:-) – Giannis Paraskevopoulos Oct 11 '13 at 16:49

convert month name in varchar to date so as to order by month

2 Answers2

Linked