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