1

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:

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
D P.
  • 1,039
  • 7
  • 27
  • 56
  • 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 Feb 09 '16 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 Feb 09 '16 at 23:52
  • 1
    `order by YEAR, MONTH(MONTH+' 1 2000'), DDATE, TIME` or create a DateTime2 from the parts and order by that http://stackoverflow.com/questions/16298624/get-a-datetime-from-year-day-of-the-year-and-hour-in-sql-server-2008-r2 – Liesel Feb 09 '16 at 23:53

2 Answers2

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

Community
  • 1
  • 1
zzbomb
  • 176
  • 6
  • 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 – D P. Feb 10 '16 at 00:03
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)
Noidz
  • 35
  • 7