sqlsql-servertimesql-order-bydatetime2

SQL Server : order by time portion of Datetime2 timestamp

I have a table of orders with a datetime2(7) column O_CreatedOn and I would like to order this recordset by the time portion only of the O_CreatedOn column.

The reason for this is that I need to generate a report that shows the most prevalent times of day when orders are taking place.

Due to the way orders have been stored in the table and the volume of orders, I do not want to break the timestamp column O_CreatedOn into O_CreatedOn_Date and O_CreatedOn_Time columns.

I tried ordering the recordset by a converted O_CreatedOn but it's still sorting on the full timestamp.

So, this: 

ORDER BY CONVERT(datetime, O_CreatedOn, 108)

did not work.

Solution

  • You can convert to time:

    ORDER BY CONVERT(time, O_CreatedOn)