Converting numeric(17,9) into a datetime

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.

3 answers

  • answered 2021-11-28 21:43 John Cappelletti

    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
    

  • answered 2021-11-28 21:45 James

    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, ...).

  • answered 2021-11-29 09:27 Charlieface

    The most performant way to do this is probably to use DATETIMEFROMPARTS with arithmetic, rather than using string manipulation.

    SELECT
      DATETIMEFROMPARTS(
        CAST(@date AS int) / 10000,
        CAST(@date AS int) / 100 % 100,
        CAST(@date AS int) % 100,
        CAST(@date * 100 AS int) % 100,
        CAST(@date % 0.01 * 10000 AS int),
        CAST(@date % 0.0001 * 1000000 AS int),
        CAST(@date % 0.000001 * 1000000000 AS int)
        )
    

    db<>fiddle

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum