How to use Order by MONTH name of data type CHAR/VARCHAR in SQL

I have seen this question "How to use Order by MONTH name of data type CHAR/VARCHAR in SQL" in one of the forum. The reason why the programmer required this option is he/she is storing Data, month and year in 3 different fields in the database. Even though I don't agree to store the dates split in three different fields I thought I will explore how we can get desired result sort by month that even a varchar/Char datatype field.

If you are an SQL developer this is not a big task. Let's check this with by creating a table.

CREATE TABLE [dbo].[Emp](

Now we will insert few records in the table.

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (1,'Asheej','March')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (2,'Priyabrata','May')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (3,'Santosh','December')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (4,'Yathish','November')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (5,'Mayur','January')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (6,'Ryan','June')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (7,'Karolina','February')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (8,'Shyam','April')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (9,'Sumit','October')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (10,'Prathibha','August')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (11,'Sowmya','July')

INSERT INTO [dbo].[Emp]([ID],[Name],[Month])VALUES (12,'Ashish','September')

GO

First we will see what will be the result if we run the simple select query order by Month.

SELECT *  FROM [dbo].[Emp] order by [MONTH]

Out of the above query will be,

ID         Name       Month
8          Shyam      April    
10         Prathibha  August   
3          Santosh    December 
7          Karolina   February 
5          Mayur      January  
11         Sowmya     July     
6          Ryan       June     
1          Asheej     March    
2          Priyabrata May      
4          Yathish    November 
9          Sumit      October  
12         Ashish     September

You can see the MONTH field order by alphebetically. So here comes the issue we want the data order by Month starting Jan, feb ..in the actual month order.

So to get that result we will write below query,

SELECT *  FROM [dbo].[Emp]

order by DATEPART(mm,CAST([MONTH]+ ' 1900' AS DATETIME)) asc

Now we will see the final output, 

ID         Name       Month
5          Mayur      January  
7          Karolina   February 
1          Asheej     March    
8          Shyam      April    
2          Priyabrata May      
6          Ryan       June     
11         Sowmya     July     
10         Prathibha  August   
12         Ashish     September
9          Sumit      October  
4          Yathish    November 
3          Santosh    December 

As usual you are always welcome to post your comment below.