如何在 SQL Server 中将时间转换为整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26407883/
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
How do I convert time into an integer in SQL Server
提问by user867621
I have a date column and a time column that are integers
我有一个日期列和一个整数的时间列
I converted the date portion like this
我像这样转换了日期部分
select convert(int, convert(varchar(10), getdate(), 112))
I thought I could do the same with this query that gives the time in HH:mm:ss
我想我可以对这个查询做同样的事情，它以 HH:mm:ss 给出时间
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)
How do I convert just the time into an integer?
如何将时间转换为整数？
采纳答案by Solomon Rutzky
Assuming you are looking for the "time" analogy to the "date" portion of your code which takes YYYYMMDDand turns it into an INT, you can:
假设您正在寻找与代码的“日期”部分类似的“时间”类比，YYYYMMDD它将其转换为INT，您可以：
- start with the
HH:mm:ssformat given by the style number 108 - remove the colons to get that string into
HHmmss - then convert that to
INT
- 以
HH:mm:ss样式编号 108 给出的格式开始 - 删除冒号以将该字符串放入
HHmmss - 然后将其转换为
INT
For example:
例如：
SELECT REPLACE(
CONVERT(VARCHAR(8), GETDATE(), 108),
':',
''
) AS [StringVersion],
CONVERT(INT, REPLACE(
CONVERT(VARCHAR(8), GETDATE(), 108),
':',
''
)
) AS [IntVersion];
回答by DeadZone
This should convert your time into an integer representing seconds from midnight.
这应该将您的时间转换为一个整数，表示从午夜开始的秒数。
SELECT (DATEPART(hour, Col1) * 3600) + (DATEPART(minute, Col1) * 60) + DATEPART(second, Col1) as SecondsFromMidnight FROM T1;
回答by Nicola Lepetit
You can use the differece between midnight and the time of the day. For example, using getdate(), you can know the percentage of the time of the day with this query:
您可以使用午夜和一天中的时间之间的差异。例如，使用 getdate()，您可以通过以下查询了解一天中的时间百分比：
select convert(float,getdate()-convert(date,getdate()))
You can then convert this number to seconds
然后您可以将此数字转换为秒
select convert(int,86400 * convert(float,getdate()-convert(date,getdate())))
You'll get the number of seconds from midnight
你会得到从午夜开始的秒数
