Question

Been Trying to figure this out and according to Cast and Convert Documentation on MSDN, this should be possible ( http://msdn.microsoft.com/en-us/library/ms187928.aspx )

看一下转换表。

""https://i.sstatic.net/KKs8Y.gif" alt="此处的内置图像描述"/"

我执行以下守则:

CREATE TABLE TableName (
    ID bigint Identity(1,1),
    SomeValue nvarchar(20) not null,
    TimestampColumn timestamp not null)

Insert Into TableName (SomeValue)
values ( testing )

SELECT Convert(decimal, TimeStampColumn) from TableName

然而,我只得到以下错误:

将数据类型时间戳转换为数字出错。

This should be possible according to the documentation or am I missing something? Note I will also need to convert back to the same timestamp value.

SELECT TimeStampColumn, Convert(timestamp, Convert(decimal, TimeStampColumn)) 
FROM TableName

最后,上述查询应得出相同的数值。

Answer 1

尝试此选项; 虽然 MSDN 说它是一个隐含转换, 但实际上不起作用。因此, 我要做的是将其转换为INT, 然后转换到小数点( INT 到小数点是默认的) 。

select val, CAST((CONVERT(bigint, timestampcol)) as decimal) as  TS as decimal  
from teststmp

Answer 2

我想这是更安全的方法

SELECT CASE 
         WHEN Cast(timestampcol AS BIGINT) >= 0 THEN 
         Cast(timestampcol AS BIGINT) 
         ELSE 18446744073709551615 + Cast(timestampcol AS BIGINT)
       END

以下显示,否则,直向偏斜的直向偏斜将折叠成负数,而负数很可能不是预期的结果。

USE tempdb;

IF DB_ID( TestDBTS ) IS NOT NULL
    DROP DATABASE TestDBTS;

CREATE DATABASE TestDBTS;


USE TestDBTS;

SELECT CAST(CAST(@@DBTS AS BIGINT) AS DECIMAL(20,0))
/*2000*/

/*Hack to manually set the timestamp. Undocumented, Unguaranteed & Dangerous!
  Do not use except on test databases.*/
DBCC WRITEPAGE(TestDBTS, 1, 9, 412, 8, 0xFFFFFFFFFFFFFF7F);


ALTER DATABASE TestDBTS SET OFFLINE WITH ROLLBACK IMMEDIATE;

ALTER DATABASE TestDBTS SET ONLINE WITH ROLLBACK IMMEDIATE;

USE TestDBTS;
SELECT CAST(CAST(@@DBTS AS BIGINT) AS DECIMAL(20,0))
/*9223372036854775807*/

CREATE TABLE T
(
X INT,
Y TIMESTAMP
)

INSERT INTO T(X) VALUES(1)

SELECT CAST(CAST(@@DBTS AS BIGINT) AS DECIMAL(20,0))
/*-9223372036854775807*/

SELECT CASE WHEN CAST(@@DBTS AS BIGINT) >= 0 
            THEN CAST(@@DBTS AS BIGINT)
            ELSE 18446744073709551615 + CAST(@@DBTS AS BIGINT) 
       END
/*9223372036854775808*/

Answer 3

尝试此 :

SELECT CONVERT(decimal, TimeStampColumn + 0)
FROM TableName

友情链接