Order by ascending date in SQL Server

elvisidrizi1
 
on Jul 19, 2021 06:37 AM
482 Views

Hallo Everyone,

I have this code that I use in SQL as stored procedures and I want that the date is in asc way from the latest date to the earliest date i.e. first of august to be the start and end of august to be the end. 

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-07' IS NULL
GROUP BY CONVERT(char(7), date, 120), FORMAT(Date, 'ddd', 'en-US') + '/' + CONVERT(VARCHAR(5),DATEPART(DAY,Date)) 
order by FORMAT(Date, 'ddd', 'en-US') + '/' + CONVERT(VARCHAR(5),DATEPART(DAY,Date)) desc

and as results in desc get this :

Total, Year, Day

4.00 2021-07 Sat/17

4.50 2021-07 Fri/9

3.50 2021-07 Fri/16

 

and as results in asc get this :

Total, Year, Day

3.50 2021-07 Fri/16

4.50 2021-07 Fri/9

4.00 2021-07 Sat/17

which doest makes sense.

Any help is much appriciated

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 20, 2021 05:14 AM

Hi elvisidrizi1,

Refer below sample query.

SQL

DECLARE @Hemingways AS TABLE(Date DATETIME, Hours DECIMAL)

INSERT INTO @Hemingways VALUES('2021-07-17',2.00)
INSERT INTO @Hemingways VALUES('2021-07-17',2.00)
INSERT INTO @Hemingways VALUES('2021-07-09',2.50)
INSERT INTO @Hemingways VALUES('2021-07-09',2.00)
INSERT INTO @Hemingways VALUES('2021-07-16',1.50)
INSERT INTO @Hemingways VALUES('2021-07-16',2.00)

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-07' 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

Output

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