Sorting by 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

Sorting by date

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-04-13 : 12:30:07

I have a table with a date column. The data looks like this:

11/22/2001 12:00:00 AM

I need to sort the data by the date in ascending order by year, then month, then day.

In my SQL query, I have:

ORDER BY Date ASC

But that's not working. Is there another way?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-13 : 12:45:32

what datatype is is stored as? if it's not working as you'd expect i'd suspect it's probably a varchar? if so, convert it to a datetime in the order by clause

Em

Apples
Posting Yak Master

146 Posts

Posted - 2008-04-13 : 12:49:17

It's datetime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 12:58:09

Can you post your full query used? Seems like you are sorting after applying some formatting with conversion to varchar type.

Apples
Posting Yak Master

146 Posts

Posted - 2008-04-13 : 13:17:09

SELECT ID, Title, CONVERT(varchar,Date,107) AS Date
FROM Events
WHERE Date > { fn CURDATE() }
ORDER BY Date ASC

I converted it to varchar so that I could format it on the front end. I guess that's what is messing it up.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 13:56:46

Try like this & see:-

SELECT ID, Title, CONVERT(varchar,Date,107) AS DisplayDate
FROM Events
WHERE Date > { fn CURDATE() }
ORDER BY Date ASC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-13 : 14:04:31

Also learn to not use ODBC function calls.

SELECT ID, Title, CONVERT(varchar, Date, 107) AS DisplayDate
FROM Events
WHERE Date > GETDATE()
ORDER BY Date ASC

E 12°55'05.25"
N 56°04'39.16"

Apples
Posting Yak Master

146 Posts

Posted - 2008-04-13 : 14:17:26

That worked visakh, thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 14:39:10

quote:
Originally posted by Apples

That worked visakh, thank you.

You're welcome. Glad that i could help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-14 : 04:52:44

quote:
Originally posted by Apples

SELECT ID, Title, CONVERT(varchar,Date,107) AS Date
FROM Events
WHERE Date > { fn CURDATE() }
ORDER BY Date ASC

I converted it to varchar so that I could format it on the front end. I guess that's what is messing it up.

It is becuase it is ordered by based on varchar value and not datetime. Thats why you sould use different alias name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources