CODEWITHSUNDEEP
sql-serverdatetimetimestamp
mohamed faisal
mohamed faisal

Reputation: 446

How to sort hours in SQL Server?

I have a table called Transaction. In that a column Time with TimeStamp datatype is found.

So the data will be looking like 2015-01-17 08:12:48.000

I want to display like 8 am

For example 

`2015-01-17 08:12:48.000`  `8 AM`
`2015-01-17 14:12:48.000`  `2 PM`

now i got the result like above. This is my result

Hour
----
01 PM
02 PM
04 PM
05 PM
06 PM
07 AM
07 PM
08 AM
09 AM
10 AM
11 AM
12 PM

This is the query for above result.

SELECT
    FORMAT(CAST(Time as datetime),'hh tt') hour,
    COUNT(TransactionNumber) Total_Transaction,
    SUM(Total) salesCost
FROM 
    [HQMatajer].[dbo].[Transaction]
WHERE 
    StoreID = '1001' 
    AND YEAR(Time) = '2015' 
    AND MONTH(Time) = '01' 
    AND DAY(Time) = '15'
GROUP BY
    FORMAT(CAST(Time as datetime),'hh tt')`

Now I want to sort the hours. It should display like

07 AM
08 AM
09 AM
10 AM
11 AM
12 PM
01 PM
02 PM
.
.
07 PM

Thanks

Upvotes: 3

Views: 3518

Answers (2)

SqlZim
SqlZim

Reputation: 38063

Try adding this to the end of your statement:

, convert(varchar(2), [time], 8)
order by convert(varchar(2), [time], 8)

Resulting in this:

SELECT
    FORMAT(CAST(Time as datetime),'hh tt') hour,
    COUNT(TransactionNumber) Total_Transaction,
    SUM(Total) salesCost
FROM 
    [HQMatajer].[dbo].[Transaction]
WHERE 
    StoreID = '1001' 
    AND YEAR(Time) = '2015' 
    AND MONTH(Time) = '01' 
    AND DAY(Time) = '15'
GROUP BY
    FORMAT(CAST(Time as datetime),'hh tt')
    , convert(varchar(2), [time], 8)
order by convert(varchar(2), [time], 8)

convert(varchar(2),[time],8) returns the datetime with style 8 in the following format: hh:mi:ss, and using varchar(2) truncates it to hh.

Documentation for convert and styles.

As Shakeer Mirza posted, using datepart() works as well.

Documentation for datepart.

Upvotes: 3

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Use DATEPART function simply

   Select DATEPART(HH, YOUR_DATETIME_COL) AS HR, ......
   ........  --Write your Statements
    ........
   ORDER BY HR

DATEPART will give result in integer format. So the Order by will give exact order

Upvotes: 3

Related Questions