sql cast of hourtime of datetime，而不会在单个数字小时内丢失前导零

CAST(DATEPART(hh, timestamp) AS varchar(2)) + ':00' AS Hour

This will get me the hour out of a timestamp field but in the case of the hours 0-9 it does not pad a leading zero and therefore when I sort by hour descending it does not sort correctly.

这将使我从时间戳字段中获取时间，但是在小时0-9的情况下，它不会填充前导零，因此当我按小时降序排序时，它不能正确排序。

Not sure what is wrong here. I specify a 2 char varchar to allow extra room for the leading zero. Hopefully there is a way to fix this without passing my field through a function that will pad the leading zero for me.

不知道这里有什么问题。我指定一个2 char varchar来为前导零提供额外的空间。希望有一种方法可以解决这个问题而不通过我的字段通过一个函数来为我填充前导零。

3 个解决方案

#1

SELECT RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(HOUR, GETDATE())), 2) + ':00';

Don't want to pad with 0s? OK, you can do it a similarly ugly way without the padding mess:

不想用0填充？好吧，你可以用一种同样丑陋的方式做到这一点而不会出现填充问题：

SELECT LEFT(CONVERT(TIME(0), GETDATE()), 2) + ':00';

Obviously replace GETDATE() with your column name. Which I hope isn't really timestamp because this is a reserved word - for a data type that has nothing to do with date or time, sadly.

显然用您的列名替换GETDATE（）。我希望这不是真正的时间戳，因为这是一个保留字 - 对于与日期或时间无关的数据类型，遗憾的是。

If you don't like either of those solutions, then just select the data from SQL Server and let your client application handle formatting/presentation details. Surely this is easy to do with C#'s format() function, for example.

如果您不喜欢这些解决方案中的任何一个，那么只需从SQL Server中选择数据并让您的客户端应用程序处理格式/表示详细信息。当然，使用C＃的format（）函数很容易做到这一点。

#2

Assuming timestamp is a column in your table

假设时间戳是表中的一列

select convert(char(3), timestamp, 108) + '00' AS Hour 
from yourtable

Alternative

替代

select left(cast(dateadd(hh, datediff(hh, 0, timestamp), 0) as time), 5)
from yourtable

Edit:

编辑：

After testing a bit i came to the conclusion that this is the fastest way (almost the performance and syntax as Aaron's solution)

经过测试后，我得出结论，这是最快的方式（几乎是Aaron解决方案的性能和语法）

SELECT RIGHT(100 + DATEPART(HOUR, timestamp) , 2) + ':00'
from yourtable

#3

You can use the TIME type which is 24 hours by default;

您可以使用默认情况下24小时的TIME类型;

cast(cast(timestamp as time) AS varchar(2)) + ':00' AS Hour

#1

SELECT RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(HOUR, GETDATE())), 2) + ':00';