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
LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott PletcherConnect With a Mentor Senior 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
All Courses

From novice to tech pro — start learning today.