How to convert date integer keys into datetime in SQL

after a lot of struggle, I cannot convert datekey (20200430) and timekey (103500) into proper datetime (2020-04-30 10:35:00). I do not have an option of joining on date dimension, the numbers must be converted using SQL functions.

What I’ve tried:

CONVERT(VARCHAR(10), CAST([datekey] AS DATE),1005) AS DATE_CONVERTED
--this is not yet datetime, but it could be start

Error I got was: Explicit int to date conversion is not allowed.

Then I tried this:

SELECT DATEADD(SECOND, TimeKey, CAST(CAST(DateKey AS VARCHAR(8)) AS DATETIME))

This just produced wrong results. I am not sure why, but it is wrong. I just want to combine datekey and timekey into datetime. My format is YYYYMMDD and HHMMSS in integers. And I need datetime.

Answer

SQL Server allows you to add together datetime values. So, you can construct the date and time separately. Here is one method:

select (convert(datetime, convert(varchar(255), date_key)) +
        convert(datetime, timefromparts(time_key / 10000, (time_key / 100) % 100, time_key % 100, 0, 0))
       ) as dt
from (values (20200430, 103500)) v(date_key, time_key);

Here is a db<>fiddle.