How to concatenate and format date and time to timestamp/datetime in Intersystems Caché database

Question

Utilizing the Intersystems Cache documentation and my limited SQL knowledge, I have figured out that Cache has it's own internal way to turn datetime into a six digit integer, but I can't figure out how to get it back to a human understandable format (the Intersystems documentation on how to do this is not comprehensible to me). I have a date column in yyyy-mm-dd, and a text time column in hh:ss xm, and I'm working in DBeaver. I need one column in datetime or datetime2.

SELECT appointment_date, CAST(appointment_start_time as TIME) ast,
       appointment_date + appointment_start_time as sdt
FROM foobar

Example:

appointment_date   ast          sdt

2016-09-21         14:30:00     64184

Desired outcome:

appointment_date   ast          sdt

2016-09-21         14:30:00     2016-09-21 14:30:00

Answer 1

You can use CAST or CONVERT, and instead of '+', use STRING for concatenation

STRING(CAST(appointment_date as VARCHAR) , ' ' , appointment_start_time)
STRING(CONVERT(VARCHAR,appointment_date,105), ' ' , appointment_start_time)

105 is for format dd-mm-yyyy