Concat Date (of type DATETIME) and Time (of Type String)

I think using COALESCE to escape the NULL values is the right solution. However, in your case it might return errors since NULL values cannot be casted. So first doing the coalesce as shown below could already help

CAST(COALESCE("tbl_xxx"."time", '00:00') AS TIME)

Next to that, PQL has its own Date conversion operator, as described here: https://docs.celonis.com/en/to_date.html. It handles both dates and timestamps, as long as you specify it in the format (see example 3):

TO_DATE ( "Table"."Timestamp" , FORMAT ( '%H:%M' ) )

Last step is to simply add them together, using the plus sign as you already mentioned.

Let me know if that worked!

Thanks for your answer, just a couple of responses:

• Having the CAST before COALESCE somehow affects the functionality of COALESCE, as you see in the last column of picture below.

• in PQL, there is no + to concat Dates, so, they should be converted to String to use the ||.
• the final result needs to be compared to a date, therefore, the final result needs to be Date...

Hi Ahmad,

I see in you example that you are using Vertica SQL instead of PQL. Which one would you like to use?

For PQL, in this case the CAST function won't work. The reason why the coalesce fails is that your time column might be an TIME instead of an string. Works this in PQL?:

COALESCE("tbl_xxx"."time", TO_DATE('00:00', FORMAT ( '%H:%M' ))

Next to that I see indeed that the '+' operator won't work. You can bypass this by first transforming the dates to an integer of milliseconds, add them together, and create add these milliseconds from t0 (= epoch):

ADD_MILLIS(

  {t0}

  ,

  REMAP_TIMESTAMPS(< your date column > , MILLISECONDS) +

  REMAP_TIMESTAMPS(< your timestamp column >, MILLISECONDS)

)

I hope this helps!