Sorting by Date

Question

Sorting by Date

kathy o

Mr or Mrs. 500

Points: 588
More actions
April 20, 2005 at 4:43 pm

#67192

I'm trying to sort a query by date. I don't want to display the time, so I change the date: CONVERT(varchar(10), dbo.Crashes.[Date], 1) . This gives the format I want mm/dd/yy, but sorts 'alphabetically' - with 01/03/2005 coming before 08/15/2004. Any ideas?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6084 More actions · Answer 1

I don't have sql on this box so can't verify but I think Sql 2k allows you to sort by something not displayed. i.e. select convert(datecol...) ... order by datecol

Otherwise, there are other options for date format (3rd parm of convert function) check BOL.

ron k SSCertifiable Points: 6053 More actions · Answer 2

What John posted should work. Just sort on the date column in the table. You can still select and convert that column for display in any format of your choice.

Select convert(varchar(10),date_field, 101) as any_name_you_like

From your_table

Order by date_field

Rob Farley SSCrazy Points: 2738 More actions · Answer 3

Or if you are needing to group by, etc... ie, you can only order by the field that's in your select query, then convert it back to a datetime field! Like.... select convert(datetime,convert(varchar(10),date_field,101),101)

And then order by the field returned.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

create table #showme

(

d1 datetime

, c1 char

)

insert into #showme values('20050301 09:30:00','a')

insert into #showme values('20040815 19:38:00','b')

select

*

, dateadd(d,0,datediff(d,0,d1)) d2

from

#showme

order by

d2

drop table #showme

d1 c1 d2

------------------------------------------------------ ---- ------------------------------------------------------

2004-08-15 19:38:00.000 b 2004-08-15 00:00:00.000

2005-03-01 09:30:00.000 a 2005-03-01 00:00:00.000

(2 row(s) affected)

Do the date format display in the front-end.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

kathy o Mr or Mrs. 500 Points: 588 More actions · Answer 5

Thank you! I think these ideas will solve the problem! I KNEW it was something right in front of my face!

Dølle Døck Mr or Mrs. 500 Points: 577 More actions · Answer 6

You could also sort the actual date like this:

SELECT CONVERT(varchar(10), dbo.Crashes.[Date], 1)

FROM dbo.Crashes

ORDER BY dbo.Crashes.[Date]

Regards,

Anders Dæmroen
epsilon.no