0

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
DAiMor
  • 3,185
  • 16
  • 24
KillerSheltie
  • 87
  • 7

1 Answers1

0

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

DAiMor
  • 3,185
  • 16
  • 24
  • Thanks. I was then able to convert to timestamp: {fn CONVERT(STRING(CAST(appointment_date as VARCHAR) , ' ' , appointment_start_time), SQL_TIMESTAMP)} – KillerSheltie Dec 14 '21 at 15:24