如何将 DateTime 转换为 Time [英] How to cast the DateTime to Time

问题描述

我使用 CAST 语法将 DateTime 字段转换为 Time.

I am casting DateTime field to Time by using CAST Syntax.

select CAST([time] as time) as [CSTTime]

日期时间2015-03-19 00:00:00.000

当前输出:时间03:05:36.0000000

我只需要 HH:MM:SS 而不是毫秒或 0000

I need only HH:MM:SS and not Milliseconds or 0000's

如何过滤或将其转换为精确的 HH:MM:SS 格式.

How to filter or Cast it to exact HH:MM:SS Format.

推荐答案

时间 未与其显示格式一起存储在 SQL Server 中.
因此，从用户的角度来看，您可以说它没有格式.
当然，这并不完全准确，因为它确实具有存储格式，但作为普通用户，您无法真正使用它.
这适用于所有日期和时间数据类型:
Date、DateTimeOffset、DateTime2、SmallDateTime、DateTime 和 Time.

Time is not stored with its display format in 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, DateTime and Time.

如果您需要一种格式，那么您不需要转换为 time 而是转换为 char.使用 Convert 获取 你需要的字符:

If you need a format then you don't need to cast to time but to a char. Use Convert to get the char you need:

SELECT CONVERT(char(10), [time], 108) as CSTTime 

如果您有兴趣，这里有一些背景数据:

Here is some background data if you're interested:

在 2000 年发表的这篇文章中，作者深入解释了 SQL Server处理日期和时间.我怀疑 SQL Server 在内部存储 date、time 和 datetime 值的方式在 2000 年到 2015 年之间是否有任何重大变化.

In this article published 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, time and datetime values internally.

如果您不想阅读全部内容，这里是相关引用:

Here are the relevant quotes, if you don't want to read all of it:

那么 SQL Server 如何在内部存储日期?它使用 8 个字节来存储日期时间值——前 4 个字节表示日期，后 4 个字节表示时间.SQL Server 可以将两组 4 字节解释为整数.
......
......
SQL Server 将时间的第二个整数存储为午夜之后的时钟滴答数.一秒包含 300 个滴答声，因此一个滴答声等于 3.3 毫秒 (ms).

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).

由于 time 实际上存储为 4 字节整数，因此它实际上没有作为数据类型的组成部分的格式.

since time is actually stored as a 4 byte integer, it really doesn't have a format as an integral part of the data type.

您可能还想查看 这篇文章 更详细的代码示例说明.

You might also want to check out this article for a more detailed explanation with code samples.

这篇关于如何将 DateTime 转换为 Time的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！