CODEWITHSUNDEEP
sqlsql-serversortingt-sql
Dawood Zaidi
Dawood Zaidi

Reputation: 288

Sort Month Name in SQL Query

My Current Query is : 

SELECT FORMAT(Date, 'MMM') as 'Date', FORMAT(Date, 'yyy') as 'Year' 
,COUNT(*) 
as 'Tickets Generated'
FROM [SambaData2].[dbo].[Tickets]
GROUP BY FORMAT(Date, 'MMM'), FORMAT(Date, 'yyy')
ORDER BY Date

It returns the values:

enter image description here

I would like the same query to return sorted month name with Year!

Upvotes: 3

Views: 3559

Answers (4)

Dhruv Rangunwala
Dhruv Rangunwala

Reputation: 226

I know this is an old thread but I wrote a query by using a temporary table for my purpose so sharing it here. Instead of a temporary table, you can use a permanent table too.

This query is useful if you want to display months in order. You can change the rank in case if you want to display months in alphabetical order.

create table #mth
(
    mth_name varchar(25),
    rank numeric
)

insert #mth
select 'JAN', 1
union
select 'FEB', 2
union
select 'MAR', 3
union
select 'APR', 4
union
select 'MAY', 5
union
select 'JUN', 6
union
select 'JUL', 7
union
select 'AUG', 8
union
select 'SEP', 9
union
select 'OCT', 10
union
select 'NOV', 11
union
select 'DEC', 12


SELECT final.Date, final.Year, final.TICKET_COUNT
FROM 
(
    SELECT FORMAT(Date, 'MMM') as 'Date', FORMAT(Date, 'yyy') as 'Year' 
    ,COUNT(*) TICKET_COUNT
    FROM [SambaData2].[dbo].[Tickets]
    GROUP BY FORMAT(Date, 'MMM'), FORMAT(Date, 'yyy')
) final
INNER JOIN #mth
ON final.Date = #mth.mth_name
ORDER BY final.Year, #mth.rank

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35563

For greater query efficiency I would avoid the format() approach. 

select
       left(datename(month,[Date]),3) [Month]
     , year([Date]) [Year]
     , [Tickets Generated]
from (
        SELECT
               dateadd(month,datediff(month,0,t.[Date]),0) as [Date]
             , COUNT(*) as [Tickets Generated]
        FROM [SambaData2].[dbo].[Tickets] AS t
        GROUP BY dateadd(month,datediff(month,0,t.[Date]),0)
    ) as d
ORDER BY [Date]

The core to this approach is the following:

dateadd(month,datediff(month,0,t.[Date]),0)

this has the effect of locating the first day of the relevant year & month, thus leaving a whole date available for the order by clause, but still grouping to the required level. Runing the foillowing may help explain

select 
  datediff(month,0,getdate()) a
, dateadd(month,datediff(month,0,getdate()),0) b
, left(datename(month,getdate()),3) c
, getdate() d


   a          b        c              d           
 ------- ------------ ----- --------------------- 
  1425    2018-10-01   Oct   2018-10-04 08:08:19

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Here is another option:

SELECT
    FORMAT(Date, 'yyy') AS Year,
    FORMAT(Date, 'MMM') AS Date,
    COUNT(*) AS [Tickets Generated]
FROM [SambaData2].[dbo].[Tickets]
GROUP BY
    FORMAT(Date, 'yyy'),
    FORMAT(Date, 'MMM')
ORDER BY
    TRY_CONVERT(datetime, FORMAT(Date, 'yyy') + '-' + FORMAT(Date, 'MMM'));

I prefer this method over the accepted answer because it is only uses components in the ORDER BY clause which were actually present in the SELECT clause. Certain RDBMS would complain about an ORDER BY using components not present in SELECT. This answer also assumes that your version of SQL Server supports TRY_CONVERT.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use year(date), month(date) in order by month() function will give you month number so you can order it easily

SELECT FORMAT(Date, 'MMM') as 'Date', FORMAT(Date, 'yyy') as 'Year',
COUNT(*) 
as 'Tickets Generated'
FROM [SambaData2].[dbo].[Tickets]
GROUP BY FORMAT(Date, 'MMM'), FORMAT(Date, 'yyy'),year(Date),month(Date)
ORDER BY year(Date),month(Date)

Upvotes: 0

Related Questions