SQL sql在日期时间之外投射小时而不在单位数小时内删除前导零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9313633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql cast hour out of datetime without dropping leading zero on single digit hours
提问by TWood
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 个字符的 varchar，以便为前导零留出额外的空间。希望有一种方法可以解决这个问题，而无需通过一个函数来为我填充前导零。
回答by Aaron Bertrand
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 timestampbecause this is a reserved word - for a data type that has nothing to do with date or time, sadly.
显然替换GETDATE()为您的列名。我希望这不是真的，timestamp因为这是一个保留字 - 遗憾的是，对于与日期或时间无关的数据类型。
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 中选择数据并让您的客户端应用程序处理格式/表示细节。format()例如，使用 C# 的函数当然很容易做到这一点。
回答by t-clausen.dk
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
回答by Alex K.
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
