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](
[ID] [nchar](10) NULL,
[Name] [nchar](10) NULL,
[Month] [nchar](10) NULL
) ON [PRIMARY]
GO
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.