Hi,
I have a column in my dataset where the dates are in two formats 21/05/2018 and the other in 2019-12-03.
when I try to convert the dates to YYYY-MM-DD format this is showing error message can some one please help me on this and also I have to
Solved! Go to Solution.
Hi Rohini
As always multipe ways you can sort this. Here is one
User a filter t split the stream, then use to DateTime tools to convert to a single type and re-union.
Hi @rohini ,
if you use a conditional statement, it should work.
If [Field1] is the field containing the dates, formula for a new field of type "Date" would be:
IF Contains("/", [Field1]) THEN
DateTimeParse([Field1],"%d/%m/%Y")
ELSE
DateTimeParse([Field1],"%Y-%m-%d")
ENDIF
Best regards
Roland
Hi @rohini
If there are only those two presentations of dates, you could filter your field based on whether it contains a /.
Those that do you could then convert to your desired format using the DateTime function.
See this attached workflow which should help.
Regards,
Tim
Hi Ronald,
thank you for this but this is converting 21/08/2019 to 2021-08-20 but it should be 2019-08-21, can you please let me know where am I going wrong
In the second of those DateTime functions, change the - to /
Should be:
IF Contains([Field1], "/") THEN
DateTimeParse([Field1], "%d/%m/%Y")
ELSE
DateTimeParse([Field1], "%Y/%m/%d")
ENDIF
I used the workflow, that is filtering the date with / and converting the same but the Union is not getting the dates accordingly.
For Eg. if the dataset has the date as 20/08/2019, when I convert that is getting converted in to 2019-08-20 but the dataset that has the date format as 2018-10-05 is not getting up dated in the new column if I rename the union to the new column name.
Please help.
Thank you.
Hi @rohini , could you post your workflow, I'll take a look at what needs changing. Alternatively if you compare it to the workflow that I posted you might be able to see for yourself what needs changing.
Regards,
Tim