Order by ascending date in SQL Server

Last Reply 16 days ago By dharmendr

Posted 17 days ago

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

You are viewing reply posted by: dharmendr 16 days ago.
Posted 16 days ago

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