SQL 如何将 DateTime 转换为 Time
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30032915/
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 to cast the DateTime to Time
提问by goofyui
I am casting DateTimefield to Time by using CASTSyntax.
我正在DateTime使用CAST语法将字段转换为时间。
select CAST([time] as time) as [CSTTime]
DateTime
2015-03-19 00:00:00.000
约会时间
2015-03-19 00:00:00.000
Present Output : Time
03:05:36.0000000
当前输出：时间
03:05:36.0000000
I need only HH:MM:SSand not Milliseconds or 0000's
我只需要HH:MM:SS而不是毫秒或 0000
How to filter or Cast it to exact HH:MM:SSFormat.
如何过滤或将其转换为精确HH:MM:SS格式。
回答by Zohar Peled
Timeis not stored with its display formatin SQL Server.
Therefore, from the user perspective, you can say that it has no format.
Of course, that's not completely accurate since it does have a storage format, but as an average user you can't really use it.
This is true for all date and time data types:Date, DateTimeOffset, DateTime2, SmallDateTime, DateTimeand Time.
Time不以其显示格式存储在 SQL Server 中。
因此，从用户的角度来看，可以说它没有格式。
当然，这并不完全准确，因为它确实有存储格式，但作为普通用户，您无法真正使用它。
这是所有的日期和时间数据类型真：Date，DateTimeOffset，DateTime2，SmallDateTime，DateTime和Time。
If you need a format then you don't need to cast to timebut to a char. Use Convertto get the charyou need:
如果您需要一种格式，那么您不需要强制转换time为char. 使用Convert以获得char您需要：
SELECT CONVERT(char(10), [time], 108) as CSTTime
Here is some background data if you're interested:
如果您有兴趣，这里有一些背景数据：
In this articlepublished in 2000 the writer explains in depth how SQL Server treats dates and times. I doubt if anything significant changed between 2000 and 2015 in the way SQL Server stores date, timeand datetimevalues internally.
在2000 年发表的这篇文章中，作者深入解释了 SQL Server 如何处理日期和时间。我怀疑，如果有任何显著的方式SQL Server存储2000和2015年之间变化date，time和datetime值在内部。
Here are the relevant quotes, if you don't want to read all of it:
以下是相关引述，如果您不想阅读全部内容：
So how does SQL Server internally store the dates?It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers.
........
........
SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms).
那么SQL Server内部是如何存储日期的呢？它使用 8 个字节来存储日期时间值——前 4 个字节表示日期，后 4 个字节表示时间。SQL Server 可以将这两组 4 字节解释为整数。
......
......
SQL Server 将时间的第二个整数存储为午夜之后的时钟滴答数。一秒包含 300 个滴答声，因此一个滴答声等于 3.3 毫秒 (ms)。
since timeis actually stored as a 4 byte integer, it really doesn't have a format as an integral part of the data type.
由于time实际上存储为 4 字节整数，因此它实际上没有作为数据类型的组成部分的格式。
You might also want to check out this articlefor a more detailed explanation with code samples.
您可能还想查看这篇文章以获取更详细的代码示例说明。
回答by Stanislovas Kala?nikovas
You can achieve it with CASTjust simple use TIME(0)datatype in following:
您可以通过以下CAST简单的使用TIME(0)数据类型来实现它：
SELECT CAST('2015-03-19 01:05:06.289' AS TIME(0))
OUTPUT:
输出：
01:05:06
01:05:06
回答by Sandip - Full Stack Developer
SQL Server 2008:
SQL Server 2008：
select cast(MyDate as time) [time] from yourtable
Earlier versions:
早期版本：
select convert(char(5), MyDate , 108) [time] from yourtable
Other Options:
其他选项：
SELECT CONVERT(VARCHAR(20), GETDATE(), 114)
The simplest way to get the time from datetime without millisecondstack is:
从没有毫秒堆栈的日期时间获取时间的最简单方法是：
SELECT CONVERT(time(0),GETDATE())
Hour and Minute
小时和分钟
SELECT substring(CONVERT(VARCHAR, GETDATE(), 108),0,6) AS Time
