Order by descending date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order by descending date

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-02-01 : 09:33:50

Here is my sp. I want it to order by descending date, but now it is 1st Feb it is putting this at the bottom, though January is still sorted fine. I need the dates to display in UK format dd/mm/yy

CREATE Procedure [dbo].[spRMU_CountNoDailyUsers]

AS
SELECT CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins
FROM tblUserLog
Where Log_Printed =0
GROUP BY CONVERT(varchar, Log_DateTime, 103) , Log_Username
ORDER BY CONVERT(varchar, Log_DateTime, 103) desc, No_Logins desc
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 09:42:22

[code]
SELECT CONVERT(varchar(10), Date_Logged_In, 103) as Date_Logged_In,
Username, No_Logins
FROM
(
SELECT dateadd(day, datediff(day, 0, Log_DateTime), 0) AS Date_Logged_In,
Log_Username as Username,
COUNT(Log_Username) AS No_Logins
FROM tblUserLog
WHERE Log_Printed = 0
GROUP BY dateadd(day, datediff(day, 0, Log_DateTime), 0), Log_Username
) d
ORDER BY Date_Logged_In DESC, No_Logins desc
[/code]

quote:
I need the dates to display in UK format dd/mm/yy

Can't you do the formating of date time in your front end application ? ?

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-02-01 : 09:56:38

Sorted like this. Thanks for your suggestion though.

CREATE Procedure [dbo].[spRMU_CountNoDailyUsers]

AS
SELECT CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins
FROM tblUserLog
Where Log_Printed =0
GROUP BY CONVERT(varchar, Log_DateTime, 103) , CONVERT(varchar, Log_DateTime, 101), Log_Username
ORDER BY CONVERT(varchar, Log_DateTime, 101) desc, No_Logins desc
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources