Date order - 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)

Date order

Author

Topic

spudhead
Starting Member

34 Posts

Posted - 2002-07-23 : 10:36:50

I'm having some problems with ordering rows from a SQL Server table by date.

The table records posts in an online forum, with the post-time logging column set to type "datetime", length of 8. Standard stuff.

The stored proc that pulls out forum posts reads:


CREATE PROCEDURE forum_showThread
@tid int
AS
SELECT
forum_posts.id,
forum_posts.body,
forum_posts.thread_id,
CONVERT(varchar(20), forum_posts.post_date) AS post_date,
forum_users.username,
forum_users.email
FROM forum_posts INNER JOIN forum_users ON forum_posts.user_id=forum_users.id
WHERE forum_posts.thread_id=@tid ORDER BY forum_posts.post_date DESC
GO

An example of what it pulls out (in order) is:

Jul 23 2002 11:35AM
Jul 23 2002 11:33AM
Jul 23 2002 11:08AM
Jul 23 2002 3:29PM
Jul 22 2002 5:12PM
Jul 22 2002 4:24PM
Jul 22 2002 4:22PM
Jul 22 2002 4:00PM
Jul 22 2002 3:36PM

As you can see, if I do this then it sees 3.29PM on July 23 as an EARLIER date value than 11.08AM on the same day.

I have checked this with several clocks and can confirm that this is, in fact, not the case.

How can I get round this? If I don't convert the date to a varchar, it comes out in the right order - but I need to convert the date otherwise... well, the date just looks silly and my client will moan.

Thanks for any suggestions.

macka
Posting Yak Master

162 Posts

Posted - 2002-07-23 : 10:51:41

Try altering your ORDER BY clause to use the same format as in the SELECT. ie:

ORDER BY CONVERT(varchar(20), forum_posts.post_date) DESC

macka.

spudhead
Starting Member

34 Posts

Posted - 2002-07-23 : 11:04:34

No, sorry, that still doesn't work. I guess it's looking at "11", seeing it as higher than "3" and ordering the rows accordingly. Can I pull the date out as a 24hr clock value?

sterobhun
Starting Member

12 Posts

Posted - 2002-07-23 : 11:08:40

Interestingly, this is how they would be ordered if the post_date was being converted to a varchar in the order by clause, because '11' is higher than ' 3'. Are you sure it's a datetime? What happens if you use something other name instead of "as post_date"?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-23 : 11:10:49

try ordering by

convert(datetime,forum_posts.post_date,13)

That converts it over to military time.

so instead of ORDER BY forum_posts.post_date DESC
use order by convert(datetime,forum_posts.post_date,13) desc

-----------------------
Take my advice, I dare ya

spudhead
Starting Member

34 Posts

Posted - 2002-07-23 : 11:15:14

Oh, this is ridiculous.

OK, so I changed it to a 24hr date value and...

23 Jul 2002 11:35:22
23 Jul 2002 11:33:50
23 Jul 2002 11:08:28
23 Jul 2002 15:29:51
22 Jul 2002 17:12:01
22 Jul 2002 16:24:00
22 Jul 2002 16:22:02

It gets the date right (oh, well done SQL Server) but still thinks that 11 in the morning comes after 3 in the afternoon??

Please, someone tell me this isn't right.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-23 : 11:17:24

could you post the exact code that comes up with that.. and could you also tell us which Database your runnning

-----------------------
Take my advice, I dare ya

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-07-23 : 12:02:42

SQL is ORDERing using the alias you have defined, "post_date", which is a varchar, and thus doesn't sort the way you would expect. Look at these examples.

Notice in SELECT #2 that I define the column as p_date and ORDER BY p_date and I get the same sort order as in SELECT #1. SELECT #3 and #4 sort the data correctly.


set nocount on

create table #tmp (Post_Date datetime )

insert #tmp (Post_Date) values ('Jul 22 2002 5:12PM')
insert #tmp (Post_Date) values ('Jul 23 2002 3:29PM')
insert #tmp (Post_Date) values ('Jul 23 2002 11:08AM')
insert #tmp (Post_Date) values ('Jul 23 2002 11:33AM')

print ''
print 'Select #1'
select CONVERT(varchar(20), post_date) AS post_date
from #tmp
ORDER BY post_date DESC

print ''
print 'Select #2'
select CONVERT(varchar(20), post_date) AS p_date 
from #tmp
ORDER BY p_date desc

print ''
print 'Select #3'
select CONVERT(varchar(20), post_date) AS formatted_post_date 
from #tmp
ORDER BY post_date desc

print ''
print 'Select #4'
select CONVERT(varchar(20), post_date) AS post_date 
from #tmp
ORDER BY convert(datetime, post_date,13) desc


--  drop table #tmp

-- here are the results
/*********
Select #1
post_date            
-------------------- 
Jul 23 2002 11:33AM
Jul 23 2002 11:08AM
Jul 23 2002  3:29PM
Jul 22 2002  5:12PM

 
Select #2
p_date               
-------------------- 
Jul 23 2002 11:33AM
Jul 23 2002 11:08AM
Jul 23 2002  3:29PM
Jul 22 2002  5:12PM

 
Select #3
formatted_post_date  
-------------------- 
Jul 23 2002  3:29PM
Jul 23 2002 11:33AM
Jul 23 2002 11:08AM
Jul 22 2002  5:12PM

 
Select #4
post_date            
-------------------- 
Jul 23 2002  3:29PM
Jul 23 2002 11:33AM
Jul 23 2002 11:08AM
Jul 22 2002  5:12PM
*****/

olily
Starting Member

37 Posts

Posted - 2002-07-23 : 21:28:42

In your ORDER clause, try to put
conver(datetime,forum_posts.post_date,13) as your order field.

spudhead
Starting Member

34 Posts

Posted - 2002-07-24 : 04:41:26

Aha. It works :) Many thanks for all your help, everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources