All geek questions in one place

ORDER BY clause in SQL Server

I recently worked on one of the select queries, in which I wanted to sort the rows based on the latest dates and times, which are stored in different columns. The client's requirement was that the time be set, so I cannot use DateTime together.

Now I have 2 questions:

  • This is not a sort until I made a change in order by condition. my initial order position:

     ORDER BY PublishDate, PublishTime DESC

    The above query worked fine, but it only sorted PublishDate and did nothing with PublishTime , I understand that it will sort first based on PublishDate and give PublishTime second preference, but with the above query it did not give preference to PublishTime , but when I changed the order order by below, it worked fine:

     ORDER BY PublishDate DESC, PublishTime DESC

    Can someone tell me what is the difference between the two queries? Why not make a primary preference for PublishDate and secondary to PublishTime ?

  • Is it possible to add user time to the DateTime column, I mean, for example, if users added a row on 31 March 2012 and entered 4:00PM , is it possible to add user time to the current date obtained using GETDATE()

+4
source share
3 answers

The previous sentence was sorted by PublishDate ASC (default).

In this example, the dataset is:

 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

In the first sentence you will receive:

 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

So far, the second sentence will create:

 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
+3
source

The default sort in the order by clause is ASC. Therefore, if you do not specify, SQL Server stands in ASC. So you really compare

 Order By PublishDate ASC, PublishTime DESC

to

 Order By PublishDate DESC, PublishTime DESC

That is why the second gives you what you want.

+5
source

As mentioned above, ASC is used for sorting by default.

For your second question, try the following:

 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
+1
source

Source: https://habr.com/ru/post/1403527/

More articles:


All Articles