Query to order (sort) by Day of Week in SQL Server

elvisidrizi1
 
on Jul 22, 2021 10:30 AM
459 Views

Hallo,

I have this code that I use in SQL and show a chart based on this code but the thing is that when I use it in ASC way or DESC way it goes by the name of the week day and not by the number of the week.

this is the code:

SELECT SUM(Hours) as 'Total'
    ,CONVERT(char(7), date, 120) AS 'year',
    FORMAT(Date, 'ddd', 'en-US') + '/' + CONVERT(VARCHAR(5),DATEPART(DAY,Date)) AS 'Day'
FROM Hemingways
WHERE CONVERT(char(7), date, 120) = '2021-07' OR '2021' IS NULL
GROUP BY CONVERT(char(7), date, 120), FORMAT(Date, 'ddd', 'en-US') + '/' + CONVERT(VARCHAR(5),DATEPART(DAY,Date)),
    CONVERT(INT,DATEPART(DAY,Date)) 
order by CONVERT(INT,DATEPART(DAY,Date)) ASC

and this is the results:

Total | year | Day

4.50 2021-07 Fri/9

3.50 2021-07 Fri/16

4.00 2021-07 Sat/17

what i want is that the results for the Day is like this :

Total | year | Day

4.50 2021-07 9/Fri

3.50 2021-07 16/Fri

4.00 2021-07 17/Sat

Your help is much appriciated.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 23, 2021 11:55 PM

Hi elvisidrizi1,

Refer below query.

SQL

DECLARE @Hemingways AS TABLE(Date DATETIME, Hours DECIMAL)
INSERT INTO @Hemingways VALUES('2021-07-17',2)
INSERT INTO @Hemingways VALUES('2021-07-17',2)
INSERT INTO @Hemingways VALUES('2021-07-09',2)
INSERT INTO @Hemingways VALUES('2021-07-09',2)
INSERT INTO @Hemingways VALUES('2021-07-16',1)
INSERT INTO @Hemingways VALUES('2021-07-16',2)
 
SELECT SUM(Hours) AS 'Total'
    ,CONVERT(CHAR(7), Date, 120) AS 'Year'
    ,CONVERT(VARCHAR(5),DATEPART(DAY,Date)) + '/' + FORMAT(Date, 'ddd', 'en-US') AS 'Day'
FROM     @Hemingways
WHERE   CONVERT(CHAR(7), Date, 120) = '2021-07' OR '2021' IS NULL
GROUP BY CONVERT(CHAR(7), Date, 120)
    ,CONVERT(VARCHAR(5),DATEPART(DAY,Date)) + '/' + FORMAT(Date, 'ddd', 'en-US')
    ,CONVERT(INT,DATEPART(DAY,Date))
ORDER BY CONVERT(INT,DATEPART(DAY,Date)) ASC

Output

Total Year Day
4 2021-07 9/Fri
3 2021-07 16/Fri
4 2021-07 17/Sat