Build a Data Time field from columns containing date, hour, minute, second

swagner
swagner Contributor
in Beast Mode

I've got a data source that has separate columns for date and time that I need to combine into a single data time field. The length of the time field differs based on the hour (sometimes 1 digit hour, sometimes 2 digit hour). I have split apart the time field based on LENGTH() into separate columns for hour, minute, and second.

Example:

Date: Jan 2, 2023

Time: 81715 (split into hour = 8, minute = 17, second = 15)

Need as an output: Jan 2, 2023 8:17:15 AM

Looking through the KB I don't see how to handle putting that all back together into a single field with date time format. Maybe I'm just missing it…

Any help would be appreciated.

Answers

  • swagner
    swagner Contributor

    I figured it out… here is the SQL for anyone else trying to do the same:

    STR_TO_DATE(CONCAT(DATE_FORMAT(Date Updated, '%b %e, %Y'), ' ', TIME_FORMAT(SEC_TO_TIME(Time Updated), '%h:%i:%s %p')), '%b %e, %Y %h:%i:%s %p')

Categories