• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Order by datetime desc, then order by the time part of the data asc?

PLease advice I have the field in the query which is the dateTime. I want to order the query by that date portion in DESC order, and time portion in ASC order
0
erikTsomik
Asked:
erikTsomik
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
ORDER BY DATEADD(DAY, DATEDIFF(DAY, 0, <datetime_col>), 0) DESC,
    DATEADD(DAY, -DATEDIFF(DAY, 0, <datetime_col>), <datetime_col>) ASC
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Unless I'm missing something here, change 'your_datetime_column_name' to whatever column you're dealing with

ORDER BY CAST(your_datetime_column_name as date) DESC,
  CAST(your_datetime_column_name as time)
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
Great . Quick response
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
Jim's should work too (assuming you're on at least SQL 2008, but since only SQL 2005 was explicitly listed, I didn't want to assume that).

It's more a matter of style.  I prefer to use things that are functional back to SQL 2005 when possible, I guess because I've got a couple of those instances I still support :) .
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Off Topic) I was wondering about the use of DATEADD, if it had something to do with performance, ergo I should add it to a bunch of places I'm supporting..
0
 
Scott PletcherSenior DBACommented:
That too, in the sense that DATEADD is clearly faster than string manipulation to strip the time.  Thus, it's a standard technique for me, so I stick to it when I can.

But I wouldn't expect either DATEADD or CAST to perform significantly better than the other, I'd consider them equal in terms of performance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now