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

Question

由于SQL Server返回的时间戳形式为'Nov 14 2011 03:12:12:947PM'，是否有一种简便方法将字符串转换为'Y-m-d H:i:s'格式的日期。

目前我使用的方法是

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

- Jayson

Answer 1

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

它只是一个连续8字节整数的十六进制表示，仅适用于确保自读取以来行未更改。

您可以读取十六进制整数，或者如果需要，使用BIGINT。例如：

SELECT CAST (0x0000000017E30D64 AS BIGINT)

结果是

400756068

在SQL Server的新版本中，它被称为RowVersion——因为这才是它真正的含义。请参阅MSDN docs on ROWVERSION：

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

所以你不能将SQL Server的TIMESTAMP转换为日期/时间——它就不是日期/时间。

但如果你说"timestamp"，但实际上你指的是DATETIME列——那么你可以使用MSDN帮助中描述的任何有效日期格式中的任何一个CAST和CONVERT主题。这些由SQL Server定义和支持。其他任何格式都不受支持，例如，您必须进行大量手动转换和连接（不推荐）。

你正在寻找的格式有点像ODBC规范（样式=121）：

DECLARE @today DATETIME = SYSDATETIME()

SELECT CONVERT(VARCHAR(50), @today, 121)

给出：

2011-11-14 10:29:00.470

SQL Server 2012将终于拥有一个FORMAT函数用于自定义格式化......

- marc_s

---

我对 SQL Server 的最大不满是什么呢？为什么称其为时间戳（timestamp），而我却无法通过它来确定时间？ - BelgoCanadian

@BelgoCanadian：问问Sybase吧 - 这是Sybase/SQL Server一直拥有的功能..... - marc_s

@BelgoCanadian，您应该很高兴地知道它现在被称为rowversion。https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017 - Jason S

有“TIMESTAMP”类型的列。 - Ghilteras

错误。它实际上是一个日期，后面跟着一个时间。https://dev59.com/bmsz5IYBdhLWcg3wFUC_#63586260 - Robert Mauro

Answer 2

最简单的方法是：

SELECT id,name,FROM_UNIXTIME(registration_date) FROM `tbl_registration`;

这将使日期列至少以可读格式显示。 此外，如果您想更改格式，请单击此处。

- Sudeep nayak

---

问题是关于 SQLServer，不是 MySQL。 - Mike M

Answer 3

使用cast函数，您可以从时间戳字段中获取日期：

SELECT CAST(timestamp_field AS DATE) FROM tbl_name

- Ali

---

这似乎无法正常工作：不允许从时间戳数据类型显式转换为日期。 - jocull

不好的想法。时间戳字段只是一个序列。你可能会得到一个日期，但这个日期没有任何意义。如果你想要一个十进制数而不是十六进制数，你应该转换为BIGINT。现在，时间戳被称为rowversion https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017 - Jason S

Answer 4

这段代码运行良好，但出现以下信息：

无法将数据类型varchar隐式转换为时间戳。 使用CONVERT函数运行此查询

所以，是的，TIMESTAMP（RowVersion）不是日期 :)

老实说，我自己也花了很多时间尝试找到一种将其转换为日期的方法。

最好的方法是将其转换为INT并进行比较。 这就是此类型的含义所在。

如果您想要一个日期 - 只需添加Datetime列即可过上幸福美满的生活 :)

祝你好运

- Mac

---

或者BIGINT，因为它占用8个字节。 - Jason S

Answer 5

"你一直在使用那个词，我不认为它意味着你认为的那样。" - Inigo Montoya

时间戳与时间没有任何关系，正如marc_s最初所说的那样。

declare @Test table (
     TestId int identity(1,1) primary key clustered
    ,Ts     timestamp
    ,CurrentDt datetime default getdate()
    ,Something varchar(max)
)

insert into @Test (Something)
    select name from sys.tables
waitfor delay '00:00:10'

insert into @Test (Something)
    select name from sys.tables

select * from @Test

注意输出中的Ts（十六进制）每个记录都增加了1，但实际时间有10秒的间隔。如果它与时间有关，那么时间戳之间应该有一个间隔来对应时间上的差异。

- jrobiii

Answer 6

我的同事帮我解决了这个问题：

select CONVERT(VARCHAR(10), <tms_column>, 112), count(*)
from table where <tms_column> > '2012-09-10'
group by CONVERT(VARCHAR(10), <tms_column>, 112);

或者

select CONVERT(DATE, <tms_column>, 112), count(*)
from table where <tms_column> > '2012-09-10'
group by CONVERT(DATE, <tms_column>, 112);

- Jadwiga

Answer 7

对我来说起作用的是： TO_DATE('19700101', 'yyyymmdd') + (TIME / 24 / 60 / 60) （Oracle数据库）

- Baidiuk Yevhen

---

问题是关于SQLServer，而不是MySQL。 - undefined

Answer 8

罗伯特·毛罗的评论是正确的。对于那些了解Sybase起源的人来说，datetime实际上是两个独立的整数，一个用于日期，一个用于时间，因此timestamp（又称rowversion）可以被视为从服务器捕获的原始值。速度更快。

- Andrew Peterson

---

在你的回答中加上评论链接可能会更有价值 :) - Paul Benn

这并没有回答问题。一旦您拥有足够的声望，您将能够评论任何帖子；相反，提供不需要询问者澄清的答案。- 来自审核 - Karl Wilhelm

@PaulBenn他指的是我的回答，不知何故被狠狠地踩了一下，这很奇怪，因为它（a）是正确的，（b）有效，而且（c）解释了原因。每一个得到赞同的说不可能的回答都是错误的。https://dev59.com/bmsz5IYBdhLWcg3wFUC_#63586260 - Robert Mauro

Answer 9

在将时间戳转换为整数后，使用CONVERT(BIGINT, [timestamp])作为Timestamp的实现后，我得到了以下结果：

446701117 446701118 446701119 446701120 446701121 446701122 446701123 446701124 446701125 446701126

是的，这不是日期和时间，而是序列号。

- user1575120

Answer 10

为什么不尝试使用FROM_UNIXTIME(unix_timestamp, format)呢？

- user3541554

---

因为这个问题涉及到微软的SQL，而不是MySQL。 - Slogmeister Extraordinaire