CODEWITHSUNDEEP
sqlsql-server
Coyolero
Coyolero

Reputation: 2433

Order by quarter of hour in the same column

I have results like this (order by Year, DayOfYear, Hour):

Year    DayOfYear   Hour
2012    266         13:00
2012    266         13:15
2012    266         13:30
2012    266         15:15
2012    266         15:30
2012    266         16:00

I need to retrive the rows order by the quarter of hour and then for hour like this:

Year    DayOfYear   Hour
2012    266         13:00
2012    266         16:00
2012    266         13:15
2012    266         15:15
2012    266         13:30
2012    266         15:30

Upvotes: 0

Views: 72

Answers (3)

Andomar
Andomar

Reputation: 238246

You can convert to 15 minute intervals with:

datediff(minute, 0, Hour) / 15 % 4

Or in a query:

select  *
,       datediff(minute, 0, Hour) / 15 % 4 as Quarter
from    YourTable
order by
        datediff(minute, 0, Hour) / 15 % 4

This prints:

Year        DayOfYear   Hour             Quarter
----------- ----------- ---------------- -----------
2012        266         13:00:00         0
2012        266         16:00:00         0
2012        266         13:15:00         1
2012        266         15:15:00         1
2012        266         15:30:00         2
2012        266         13:30:00         2

(6 row(s) affected)

Upvotes: 0

Vikdor
Vikdor

Reputation: 24134

You can use DATEPART(mi, CONVERT(DATETIME, hour)) in your SELECT list and sort on this.

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280479

Essentially you can just order by the minute, since this will be 0, 15, 30, or 45.

ORDER BY Year, DayOfYear, DATEPART(MINUTE, Hour), Hour;

Upvotes: 3

Related Questions