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:

1. 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?

2. 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()

Answer 1

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.