date sort WILL NOT work - 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

Import/Export (DTS) and Replication (2000)

date sort WILL NOT work

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-06 : 12:01:02

Morning,

I have been working on this what seem like forever. I tried writing the query like this:


SELECT IssueID,IssueNumber,Vendor,ResolutionDate,DeptName
FROM dbo.vwAcctIssue 
where Department = 'AP'
order by ResolutionDate desc

I get a wild mixture starting with Jan 5 2006 then the next one is Dec 19 2005 the last one in the list is Jan 3rd.

The field type is datetime. An example of some data is 2005-12-23 00:00:00.000

I've tried converting the field then sorting on it like so:
convert(varchar,ResolutionDate,101)as ResolutionDate

And nothing works! Can someone point me in the right direction?

Thanks in advance

Laura

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 12:30:38

Are you sure it's not in the correct order? You don't say what the year is for Jan 3rd but the other two are correct.

If ResolutionDate is a datetime then it should work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-01-06 : 13:06:45

Yes, its incorrect. Jan 3rd 2005. I want it to go Oldest to latest. Thats not what it doing. I have to pull this data out of Access and dump it into SQL. It is causing me no end of headaches.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 13:28:23

Get rid of the desc keyword then - that is causing it to order by latest first.

order by ResolutionDate
or if you wish
order by ResolutionDate asc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mcarahul
Starting Member

11 Posts

Posted - 2006-01-08 : 23:45:25

Try using this conversion "convert(datetime,convert(varchar(10),ResolutionDate,101),101)"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-09 : 00:51:42

quote:
Originally posted by lauramayer

The field type is datetime. An example of some data is 2005-12-23 00:00:00.000

quote:
Originally posted by mcarahul

Try using this conversion "convert(datetime,convert(varchar(10),ResolutionDate,101),101)"

This is not necessary as the field is datetime

Laura, as suggested by nr, drop the desc

SELECT IssueID,IssueNumber,Vendor,ResolutionDate,DeptName
FROM dbo.vwAcctIssue 
where Department = 'AP'
order by ResolutionDate desc

-----------------
'KH'
Selamat Hari Raya Haji

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources