Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • 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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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