Solved

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

Posted on 2014-09-16
6
198 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

777 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