How to sort multiple columns in SQL Server according to the date

Question

I am new to SQL Server. Can you please tell me how can I sort this table according to the Year, Month, Day, Time? So that I can display other columns like "NAME" in the correct ascending order of the date.

I was using the following SQL query, but this returns result in ascii character order:

SELECT * 
FROM table_name 
WHERE YEAR = '2016' 
ORDER BY YEAR, MONTH, DDATE, TIME

Doesn't sort by date. Can you please show me with an example how to achieve this.

I have a SQL Server table that looks like this:

You need to combine year, month, ddate, and time into a single value such as the julian date or minutes since epoch and sort on that. Here is a calculator http://aa.usno.navy.mil/data/docs/JulianDate.php — Marichyasana
– Marichyasana, Commented Feb 9, 2016 at 23:49
The Month part is messing you up; March should be 3, April should be 4, etc. As Marichyasana infers, you could create a computed column made up of a concatenation of those values, like 20160330, 20160304, etc. — B. Clay Shannon-B. Crow Raven
– B. Clay Shannon-B. Crow Raven, Commented Feb 9, 2016 at 23:52
order by YEAR, MONTH(MONTH+' 1 2000'), DDATE, TIME or create a DateTime2 from the parts and order by that stackoverflow.com/questions/16298624/… — Liesel
– Liesel, Commented Feb 9, 2016 at 23:53

Community · Accepted Answer · 2017-05-23 10:32:33Z

1

As B. Clay suggested month is probably not doing what your thinking, since it looks like that's a varchar or similar it's just going to order it alphabetically.

try something like this:

SELECT * FROM table_name 
where YEAR = '2016' order by 
YEAR, DATEPART(MM,MONTH + ' 1 2016'), DDATE, TIME

Fiddle: http://sqlfiddle.com/#!6/e345e/9

Related: Convert month name to month number in SQL Server

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Feb 9, 2016 at 23:55

zzbomb

1766 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

D P. Over a year ago

Thank you for both Zoidz and zzbomb. After combing this code with CONVERT(Int,DDATE), CONVERT(TIME, TIME) it worked well. Thanks for both of you for showing me with an example

Noidz · Accepted Answer · 2016-02-09 23:55:27Z

1

First of all. You should use only one column for a date whenever is possible.

But in your case, your columns are VARCHAR type. Try something like this.

SELECT * FROM table_name where YEAR = '2016' 
order by 
   CONVERT(Int,YEAR), 
   (CASE MONTH
        WHEN 'January' THEN 1
        WHEN 'February' THEN 2
        WHEN 'March' THEN 3
        WHEN 'April' THEN 4
        WHEN 'May' THEN 5
        WHEN 'June' THEN 6
        WHEN 'July' THEN 7
        WHEN 'August' THEN 8
        WHEN 'September' THEN 9
        WHEN 'October' THEN 10
        WHEN 'November' THEN 11
        WHEN 'December' THEN 12), 
  CONVERT(Int,DDATE), 
  CONVERT(TIME, TIME)

answered Feb 9, 2016 at 23:55

Noidz

357 bronze badges

Collectives™ on Stack Overflow

How to sort multiple columns in SQL Server according to the date

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related