如何将 SQL Server 的时间戳列转换为日期时间格式

2021-12-02 00:00:00 datetime timestamp sql-server

当 SQL Server 返回类似 'Nov 14 2011 03:12:12:947PM' 的时间戳时，是否有一些简单的方法可以将字符串转换为日期格式，例如Ymd H:i:s".

As SQL Server returns timestamp like 'Nov 14 2011 03:12:12:947PM', is there some easy way to convert string to date format like 'Y-m-d H:i:s'.

到目前为止我使用

date('Y-m-d H:i:s',strtotime('Nov 14 2011 03:12:12:947PM'))

推荐答案

SQL Server 的 TIMESTAMP 数据类型与日期和时间无关！

SQL Server's TIMESTAMP datatype has nothing to do with a date and time!

它只是一个连续的 8 字节整数的十六进制表示 - 它只有助于确保一行在被读取后没有改变.

It's just a hexadecimal representation of a consecutive 8 byte integer - it's only good for making sure a row hasn't change since it's been read.

您可以读取十六进制整数，或者如果您想要BIGINT.举个例子:

You can read off the hexadecimal integer or if you want a BIGINT. As an example:

SELECT CAST (0x0000000017E30D64 AS BIGINT)

结果是

400756068

在较新版本的 SQL Server 中，它被称为 RowVersion - 因为它确实是这样.请参阅有关 ROWVERSION 的 MSDN 文档:

In newer versions of SQL Server, it's being called RowVersion - since that's really what it is. See the MSDN docs on ROWVERSION:

是一种公开数据库中自动生成的唯一二进制数的数据类型.rowversion 通常用作一种机制用于版本标记表行.这rowversion 数据类型只是一个递增的数字，而不是保留日期或时间.要记录日期或时间，请使用 datetime2数据类型.

Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

因此您不能将 SQL Server TIMESTAMP 转换为日期/时间 - 它只是不是日期/时间.

So you cannot convert a SQL Server TIMESTAMP to a date/time - it's just not a date/time.

但如果您说的是时间戳，但实际上您指的是 DATETIME 列 - 那么您可以使用 CAST 和 CONVERT 主题.这些是由 SQL Server开箱即用"定义和支持的.不支持其他任何内容，例如您必须进行大量手动转换和连接(不推荐).

But if you're saying timestamp but really you mean a DATETIME column - then you can use any of those valid date formats described in the CAST and CONVERT topic in the MSDN help. Those are defined and supported "out of the box" by SQL Server. Anything else is not supported, e.g. you have to do a lot of manual casting and concatenating (not recommended).

您要查找的格式有点像 ODBC 规范(样式 = 121):

The format you're looking for looks a bit like the ODBC canonical (style = 121):

DECLARE @today DATETIME = SYSDATETIME() SELECT CONVERT(VARCHAR(50), @today, 121)

给出:

2011-11-14 10:29:00.470

SQLServer 2012 终于会有FORMAT 功能来做自定义格式......

SQL Server 2012 will finally have a FORMAT function to do custom formatting......

相关文章