Solved

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

Posted on 2014-09-16
6
205 Views
Last Modified: 2014-09-16
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
Comment
Question by:erikTsomik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40325942
ORDER BY DATEADD(DAY, DATEDIFF(DAY, 0, <datetime_col>), 0) DESC,
    DATEADD(DAY, -DATEDIFF(DAY, 0, <datetime_col>), <datetime_col>) ASC
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40325949
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
 
LVL 19

Author Closing Comment

by:erikTsomik
ID: 40325962
Great . Quick response
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40326000
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40326007
(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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40326275
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question