在SQL Server数据库中将长日期时间转换为实际日期时间的最佳方法是什么?

Question

在我的Microsoft SQL Server数据库中，每个日期或日期时间都表示为decimal(17,0)值。

例如:20210722054500000将转换为2021-07-22 05:45:00:000

我已经想出了一个解决方案来转换这个:

SELECT 
CONVERT(datetime, (SUBSTRING(CAST(20210722054500000 AS varchar(20)), 1, 4) + '-' + 
SUBSTRING(CAST(20210722054500000 AS varchar(20)), 5, 2) + '-' + 
SUBSTRING(CAST(20210722054500000 AS varchar(20)), 7, 2) + ' ' + 
SUBSTRING(CAST(20210722054500000 AS varchar(20)), 9, 2) + ':' + 
SUBSTRING(CAST(20210722054500000 AS varchar(20)), 11, 2) + ':00'), 120)

它可以工作，但我觉得应该有一个更好的解决方案来转换这个

所以我的问题是，有没有人需要更少的代码或更好的性能的方法?

Answer 1

可以不使用强制类型转换和字符串函数，使用datetimefromparts:

declare @input decimal(17,0) = 20210722054500000
select DATETIMEFROMPARTS(
@input/10000000000000      ,-- year
@input/100000000000 % 100  ,-- month
@input/1000000000 % 100    ,-- day
@input/10000000 % 100      ,-- hour
@input/100000%100          ,-- minute
@input/1000%100            ,-- seconds
@input%1000                -- milliseconds 
)

Answer 2

一种方法是使用一组简单的子字符串提取部件。给定此表和数据:

CREATE TABLE dbo.WhoDesignedThis
(
Id int,
TheDate decimal(17,0) -- this was hard to write with a straight face
);
INSERT dbo.WhoDesignedThis(Id, TheDate) VALUES
(1, 20210722054500000),
(2, 19991231132247699);

这个查询:

;WITH x AS
(
SELECT Id, TheDate, x = CONVERT(char(17), TheDate) 
FROM dbo.WhoDesignedThis
)
SELECT Id, TheDate, output = DATETIMEFROMPARTS
(
LEFT(x,4), 
SUBSTRING(x,5,2), 
SUBSTRING(x,7,2), 
SUBSTRING(x,9,2), 
SUBSTRING(x,11,2), 
SUBSTRING(x,13,2), 
RIGHT(x,3)
)
FROM x;

产生以下结果:

2021-07-22 05:45:00.0001999-12-31 13:22:47.700