I am combining date and time in one column but once is combined the date format is ok but my time format is all zeros. is there something I am missing?
How are you combining your date and time fields? What format is your time field in?
DATE_ADD(`Timecard_Pay_Date`, interval time_to_sec(`Time_Out`) second)
Its converting your time string to the number of seconds and then adding that to your date since dates have an inherent time of midnight
Thanks Grant for your prompt answer. My date is formatted as date. After I combined the date and time, i changed it to timestamp.
What is the format of your Time_Out field?
As a time stamp i got the example above.
I changed it to text and now i got this:
I need to get it in this format:
Thanks again Grant.
@Liliana If you have access to ETL 2.0 then you can add a formula tile with Date and combine with a couple of functions as shown below:
Raw data: Thu Sep 02 00:00:00 GMT 2021 07:49 AM
Output: 2021-09-02 07:49:00 (format in timestamp)
Formula: timestamp(CONCAT(split_part(`Date`,' ',3), ' ', split_part(`Date`,' ',2), ' ',split_part(`Date`,' ',6),' ',split_part(`Date`,' ',7),' ',split_part(`Date`,' ',8),' ',split_part(`Date`,' ',5)))
The idea here is to rearrange texts in your string in a format that can be recognized by domo and converted to timestamp. You can also use Split Columns tile and combine tiles to do the same.
Hi @Liliana ,
Rather than trying to combine the date and time as text, I would suggest using this approach:
Hope this helps!
Thank you @tejusarora and @MichelleH for your answers. I will try your suggestions.
Glad I could help @Liliana. If you could accept my answer so others can find it easier I'd appreciate it! Thanks.