How to convert date integer keys into datetime in SQL

user_14449796 0
Viewed 747

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.

sql-server-2012 sql-server sql
2 Answers

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.

user_11440350

You need to turn the values into an unambiguoys format, and then convert. You can use STUFF to inject the needed : and - characters in:

SELECT CONVERT(datetime2(0),STUFF(STUFF(STUFF(STUFF(CONCAT(datekey,'T',RIGHT('000000' + timekey,6)),14,0,':'),12,0,':'),7,0,'-'),5,0,'-'),126)
FROM (VALUES(20200430,103500))V(datekey,timekey);
user_20299830
Related