In my Microsoft SQL Server database, every date or datetime is represented as a decimal(17,0) value.

For example: 20210722054500000 would translate to 2021-07-22 05:45:00:000

I already came up with an solution to convert this:

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)

It works, but I feel there should be an better solution to convert this.

So my question is has anybody an approach that requires less code or would be better in regards to performance?

CodePudding user response：

One way is to extract the parts using a simple set of substrings. Given this table and data:

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

This query:

;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;

Produces these results:

• Example db<>fiddle

CodePudding user response：

You can do it without casts and string functions, using 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 
)