community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Converting and filtering dates

Highlighted
Meteor

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 

ACE Emeritus
ACE Emeritus

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. 

 

 date.PNG

Alteryx Everything, Leave no one behind.
Alteryx Certified Partner
Alteryx Certified Partner

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

 

Meteor

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

 

2019-09-26.PNG

Meteor

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

Meteor

@GavinAttard 

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

Alteryx Certified Partner
Alteryx Certified Partner

Should be:

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

 

Meteor

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.

Meteor

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

 

Labels