Converting numeric(17,9) into a datetime-CodePudding

In a data warehouse, I am building I am receiving DateTime data from one of our sources in numeric(17,9) format. Is there a way in SQL Server to convert the numeric to a DateTime and whilst retaining the time information? For example, a date such as 20210928.110424000 should be converted to 2021-09-28 11:04:24:000. I am able to convert this to 2021-09-28 00:00:00.000 but as you can see I am missing the time information.

CodePudding user response：

A small matter with STUFF(). You only need to be concerned with the time portion... yyyymmdd will convert to a date.

Example

Declare @V numeric(17,9) = 20210928.110424000
Select try_convert(datetime,stuff(stuff(stuff(stuff(@V,16,0,'.'),14,0,':'),12,0,':'),9,1,' '))

Results

2021-09-28 11:04:24.000

CodePudding user response：

Another way to do it, using substring would be:

declare @date numeric(17,9) = 20210928.110424000

-- mask 2021-09-28 11:04:24.000 (121)
select 
  convert(datetime,
    substring(convert(varchar(20),@date),1,4)   '-'  
    substring(convert(varchar(20),@date),5,2)   '-'  
    substring(convert(varchar(20),@date),7,2)   ' '  
    substring(convert(varchar(20),@date),10,2)   ':'  
    substring(convert(varchar(20),@date),12,2)   ':'  
    substring(convert(varchar(20),@date),14,2)   '.'  
    substring(convert(varchar(20),@date),16,3)
  ,121)

You can test on this db<>fiddle

Nevertheless, in traditional BI environments, the dates used to be linked to date/time dimensions (with surrogate keys numeric), rather than date/datetime. This strategy allows you to group the data from the dimension, where for a given surrogate key (i.e. YYYYMMDD) you have any other attributes associated (year, quarter, semester, month, week, ...).