Converting and filtering dates

[rohini]

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 

[GavinAttard · Accepted 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. 

 

[RolandSchubert]

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

[TSP · Accepted solution]

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

[rohini]

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

[TSP]

@GavinAttard 

In the second of those DateTime functions, change the - to /

[RolandSchubert]

Should be:

IF Contains([Field1], "/") THEN
DateTimeParse([Field1], "%d/%m/%Y")
ELSE
DateTimeParse([Field1], "%Y/%m/%d")
ENDIF

[rohini]

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.

[TSP]

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