Issue trying to convert a timestamp field to the correct date format

Hello all,

I am having trouble correcting the date format. In the attached flow, there is a field called recordTimeStamp. When I had imported the .hyper file into Tableau and using the formula Date(recordTimeStamp), it displays the date as follows:

8/2/2023

9/2/2023

10/2/2023

I checked the weekdays for those dates, and it's reading it as August 2nd, September 2nd, and October 2nd. It has the months and days switched. It should be 2/8/2023, 2/9/2023, and 2/10/2023 with the weekdays being Wednesday, Thursday, Friday, respectively. On the CSV output file, it looks correct but with the .hyper output file in Tableau, it's incorrect. I tried to use the DateTime tool to try to correct, but the output datetime field shows NULL. If anyone can please offer suggestions, would greatly appreciate it. Thank you!

TEST Flow.yxzp

Date Time

Accepted answers

binu_acs

@synergy825 Seems to be your date field has mixed date formats. Some are coming with the format dd/MM/yyyy and some are yyyy-mm-dd etc. Replace your DateTime tool with the below formula tool

DateTime_130223.yxmd

All comments

binu_acs

@synergy825Update your DateTime tool with the format dd/MM/YYYY

synergy825

Hi @binu_acs,

On the workflow, I changed it to what you suggested, and got this error on the output:

ConvError: DateTime (22): DateTime_Out: Cannot convert "2023-02-08 12:00:32" to a date/time with format "%d/%m/%Y" and language "English": Expected separator '/%m/%Y', got: '23-02-08 12:00:32' Record #10

binu_acs

@synergy825 Seems to be your date field has mixed date formats. Some are coming with the format dd/MM/yyyy and some are yyyy-mm-dd etc. Replace your DateTime tool with the below formula tool

DateTime_130223.yxmd

synergy825

Thank you @binu_acs ! This worked.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703