Sort by DateName doesn't work in SQL

Question

I am trying to calculate the total hours worked by each employee per month.

Following is the code which i used:

SELECT 
    emp_name, 
    emp_id, 
    monthval = DATENAME( month, Pdate), 
    Total_Time = (CAST(SUM(DATEDIFF(MI, In_Punch, Out_Punch))/60 as varchar)            
           + ':' +  right('00'+ cast(SUM(DATEDIFF(MI, In_Punch, Out_Punch))%60 as varchar), 2)) 
FROM masterprocessdailydata 
WHERE Emp_Id = @EmpID 
GROUP BYEmp_Name,Emp_ID, DATENAME(month, Pdate)

It working fine apart from that the order of month shown is different.

This query orders the columns alphabetic wise.

Output:

April, December, February, January,July and so on...

Expected Output:

January, February, March...

Since i want the month name as output, i cant use - DATEPART(month, Pdate).

Is there a way to convert monthname to monthvalue, just for grouping purpose? Or will i have to change the query completely?

why am i getting negative votes for the question i posted? – Ashish Sah Sep 23 '15 at 14:04 — Ashish Sah, Sep 23 '15 at 14:04

Jeff Siver · Answer 1 · 2015-09-23T13:22:03.310

0

Just add an order by along with adding the additional column to the results (sorry, forgot that part earlier). Since the DatePart and DateName functions are similarly consistent, this won't impact how the results are generated:

SELECT emp_name, emp_id, Datepart(month, Pdate), monthval = DATENAME( month, Pdate) , Total_Time =    (CAST(SUM(DATEDIFF(MI, In_Punch, Out_Punch))/60 as varchar) + ':' +  right('00'+ cast(SUM(DATEDIFF(MI, In_Punch, Out_Punch))%60 as varchar), 2)) 
  from masterprocessdailydata 
  Where Emp_Id = @EmpID 
  group by Emp_Name,Emp_ID, Datepart(month, Pdate), DATENAME(month, Pdate)
  order by Datepart(month, Pdate)

edited Sep 23 '15 at 13:22

answered Sep 23 '15 at 13:07

Jeff Siver

7,434
30
32

It isnt working. I had tried it before posting the question. the error says: "Column "masterprocessdailydata.PDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. " – Ashish Sah Sep 23 '15 at 13:15
you can add month number in sql select clause. why not adding them ? – Ameya Deshpande Sep 23 '15 at 13:18
Updating the question with the new SQL would help! – MiguelH Sep 23 '15 at 13:19

score 0 · Answer 2 · edited Sep 23 '15 at 14:33

This query worked:

SELECT emp_name, emp_id, Datepart(month, Pdate), 
monthval = DATENAME( month, Pdate) ,DATENAME( month, Pdate),
 Total_Time =    (CAST(SUM(DATEDIFF(MI, In_Punch, Out_Punch))/60 as varchar) + ':' +  right('00'+ cast(SUM(DATEDIFF(MI, In_Punch, Out_Punch))%60 as varchar), 2)) 

 from masterprocessdailydata 
 Where Emp_Id = @EmpID 
 group by Emp_Name,Emp_ID, Datepart(month, Pdate), DATENAME(month, Pdate)
 order by Datepart(month, Pdate)

Thanks!

Sort by DateName doesn't work in SQL

2 Answers2