# 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.

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

Another way to do it, using `substring` would be:

``````declare @date numeric(17,9) = 20210928.110424000

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

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