Adding "H:mm:ss" values in one field to a DATE field
Answered
I’m sorry if the title is a little hard to follow; I’ll do my best to explain better:
I’ve got two fields, a DATE field in the DATE format, i.e.: “Dec 27, 2019 12:00:00 AM”, and a TIME field in a STRING format, i.e.: “17:01:00” which is the time of day on the aforementioned date. I need to essentially replace the “12:00:00AM in the former with the time from the latter, i.e.: “Dec 27, 2019 17:01:00”.
I thought maybe CONCAT() wrapped in a FORMATDATE(), but when I concatenate the two fields, I get this: “Fri Dec 27 00:00:00 CST 201917:01:00”, and any attempt to FORMATDATE() that value gives me the “function argument not compatible: Is ‘STRING’ but expected ‘DATE’” error message. Can anyone offer any ideas on how I might do this?
-
Official comment
Mario,
Good afternoon!
First, we'll need to convert the DATE into a STRING while stripping the existing TIMESTAMP:
- FORMATDATE(#DateValue;"MMM dd, Y")
Next, we can concatenate the STRING output from above with the TIMESTAMP already represented as a STRING:
- CONCAT(#FormatDateOutput;" ";#TimeValue)
The output from the above will be what you are looking for, but represented as a STRING (E.g. "Dec 27, 2019 17:01:00"). Should you be outputting to something that does not care about the casting of this value, you can call it good here.
If you would like to convert the STRING output back into DATE format, you can use the following:
- ASDATE(#ConcatOutput;"MMM dd, Y HH:mm:ss")
Finally, you can do all of the above in one wrapped function. The first will convert back to DATE while the second will remain in STRING format:
- ASDATE(CONCAT(FORMATDATE(#DateValue;"MMM dd, Y");" ";#TimeValue);"MMM dd, Y HH:mm:ss")
- CONCAT(FORMATDATE(#DateValue;"MMM dd, Y");" ";#TimeValue)
Cheers,
Brian
Comment actions
Please sign in to leave a comment.
Comments
3 comments