Skip to content
Advertisement

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:

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

Then I tried this:

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.

Advertisement

Answer

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

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement