How to order by dates? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

How to order by dates?

Author

Topic

jonasdavedomingo
Starting Member

33 Posts

Posted - 2004-12-03 : 05:12:09

How do you ORDER BY DATES descendingly?

Let's say i have a column named log_Date and the format is 12/03/04 1:04 PM

Is there an easier way in ordering that kind of date format? If so, what is the code for that?

If there's none, then I'm thinking of substringing the month day and year.
First you substring the year first because you need to check the year first, then followed by the month then day.

I tried

ORDER BY LEFT(6, 8,  log_Date) DESC, LEFT(0, 2, log_Date) DESC, LEFT(3, 5, log_Date) DESC

but that code doesnt work.

Please help! thanks!

Andraax
Aged Yak Warrior

790 Posts

Posted - 2004-12-03 : 05:26:04

Are you using the datetime datatype? What's wrong with the ORDER BY function? If you are using varchar's to represent dates, then I advise switching to datetime.

jonasdavedomingo
Starting Member

33 Posts

Posted - 2004-12-03 : 06:04:04

quote:
Originally posted by Andraax

Are you using the datetime datatype? What's wrong with the ORDER BY function? If you are using varchar's to represent dates, then I advise switching to datetime.

i made it.
i tried ORDER BY convert(datetime, log_Date) DESC and it works. thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-05 : 08:27:58

Why are you storing datetime data in a varchar instead of datetime?

HTH

=========================================
Let X = {All sets s such that s is not an element of s}

(X element of X) ==> (X not element of X)
(X not element of X) ==> (X element of X)

(Bertrand Russell Paradox)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-12-05 : 14:35:01

If your dates are stored in a character based data type you can order them if they are stored in ISO format i.e. YYYYMMDD but as everyone else points out above you would probably be better using a datetime

steve

To alcohol ! The cause of - and solution to - all of life's problems

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources