如何在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

所以数据应该是2015-01-17 08:12:48 000

I want to display like 8 am

我想在早上8点左右显示

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

谢谢

2 个解决方案

#1

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.

convert(varchar(2)，[time]，8)返回带有风格为8的datetime(8)，格式如下:hh:mi:ss，使用varchar(2)将其截断为hh。

Documentation for convert and styles.

转换和样式的文档。

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

正如Shakeer Mirza所言，使用datepart()也很有效。

Documentation for datepart.

datepart文档。

#2

Use DATEPART function simply

使用DATEPART函数简单地

   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

DATEPART将以整数格式给出结果。所以顺序会给出精确的顺序。

#1