ORDER BY clause in SQL Server

Question

Recently I was working one of the select query, wherein I wanted to sort the rows based on latest date and time which are stored in different columns. The requirement by client was that the time will be custom, so I cannot use the DateTime together.

Now I have 2 questions:

It was not sorting until I made the changes in order by clause. my original order by clause was:
```
ORDER BY PublishDate, PublishTime DESC 
```
The above query was working fine, but was only sorting the PublishDate, and doing nothing with PublishTime, I understand that it will primarily sort on the basis of PublishDate, and would give second preference to PublishTime, but with the above query it wasn't giving any preference to PublishTime, but when I changed the order by clause to below it worked fine:
```
ORDER BY PublishDate DESC, PublishTime DESC 
```
Can anyone tell me what's the difference between the two queries? Why don't both give primary preference to PublishDate and secondary to PublishTime?
Is it possible to append the custom time to a DateTime column, I mean say for example if users added a row on 31 March 2012, and entered 4:00PM, is it possible to add the custom time to the the current date retrieved using GETDATE()

Datetime columns already have the time inside them. Did you make PublishDate a Date, or a DateTime? Date: select publishDate + ' ' + publishTime DateTime: select cast(cast(publishDate as Date) as varchar) + ' ' + publishTime — Brian White
– Brian White, Commented Mar 29, 2012 at 16:20

Brian White · Accepted Answer · 2012-03-26 19:52:16Z

The default sort in an order by clause is ASC. So if you don't specify, SQL Server sticks in ASC. So you're really comparing

Order By PublishDate ASC, PublishTime DESC

to

Order By PublishDate DESC, PublishTime DESC

That's why the second one is giving you what you want.

Joel · Accepted Answer · 2012-03-26 19:54:26Z

The former clause was sorting by PublishDate ASC (the default direction).

In this example data set:

PublishDate PublishTime 1/1/2012 01:00 12/1/2011 03:00 1/1/2012 03:00 1/1/2012 01:30 1/3/2012 01:30 1/5/2012 01:30

The first clause would produce:

PublishDate PublishTime 12/1/2011 03:00 1/1/2012 03:00 1/1/2012 01:30 1/1/2012 01:00 1/3/2012 01:30 1/5/2012 01:30

While the second clause would produce:

PublishDate PublishTime 1/5/2012 01:30 1/3/2012 01:30 1/1/2012 03:00 1/1/2012 01:30 1/1/2012 01:00 12/1/2011 03:00

Colin · Accepted Answer · 2012-03-26 20:52:03Z

As the others mentioned, The default sort for order by is ASC.

For your second question, try this:

declare @newDateTime as datetime declare @userTime as time set @userTime = '4:00PM' select @newDateTime = CONVERT(varchar(10), getDate(), 101) +' '+ convert(varchar(8), @userTime,108) select @newDateTime --current date + user time entry

Collectives™ on Stack Overflow

ORDER BY clause in SQL Server

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related