Date Conversion Error

[SH_94]

Dear Community,

I encountered the conversion error on the date format which consist of following date format: 

Error in Alteryx 

Below is the format used to convert the date format accordingly.

Formula in the text format :

IF !IsNull(DateTimeParse([Invoice Date],"%d/%m/%y")) 

// Check whether it falls in the date format

THEN DateTimeParse([Invoice Date],"%d/%m/%y") 

// If yes then parse it with a new format

ELSEIF 

!IsNull(DateTimeParse([Invoice Date],"%d-%b-%y")) THEN DateTimeParse([Invoice Date],"%d-%b-%y")

ELSEIF 

!IsNull(DateTimeParse([Invoice Date],"%Y-%m-%d")) THEN DateTimeParse([Invoice Date],"%Y-%m-%d")

ELSEIF 

!IsNull(DateTimeParse([Invoice Date],"%Y-%m-%d")) THEN ToDate(DateTimeParse([Invoice Date],"%Y-%m-%d"))

ELSE Null() ENDIF // Returns null if its a new format

Could anyone enlighten me on which part that i have made the error and how to correct it?

Many thanks in advance

[apathetichell]

I think you are trying to convert across multiple types of dates in the same column - but your datetimeparse() optionality is just creating errors. The true/false checking shouldn't use datetimeparse since it's not creating a null per se -it's creating an error. The better way to do this is with a regex_match function - and I'll upload a workflow to show you how to do this in a few minutes...

Ignore the yucky error messages... I think it goes through the values for each part of the switch but only runs the relevant section... regex wasn't going to work because it can't tell the difference between 19/11/26 and 11/26/19  - at least not easily...

Notes - your integer is off by two days according to Alteryx. I cut down your format so the switch was clearer.

[DawnDuong]

Hi @SH_94 

you have multiple date formats in your data. So you need to identify all the possible formats, assign each record to a format type and then use a switch or other method to ensure that different conversion applies to different format types 

dawn 

[SH_94]

Dear @apathetichell ,

May i know if currently there is no format column, how will you write the formula? Could you please guide me on this?

Many thanks in advance.

[DawnDuong]

Hi @SH_94 

you can concatenate the known formats like what is shown in the screenshot here (i only included  3 types - leaving the rest for you to try. Can check out the link here for the syntax and specifiers https://help.alteryx.com/current/designer/datetime-functions).

The warnings are not errors and the results are still correct.

[apathetichell]

Did you check the workflow I posted? I used a switch command (after cleaning your format identifier column) with the various date formats.

@DawnDuong - Since OP has dates with the same character arrangement and same punctuation representing different date units (ie 11/26/19 and 19/11/26) I do not believe that they can use this data without a format key.

[DawnDuong]

hi @apathetichell 

True, indeed. Must use the format key field that is provided there and probably use a switch ha.

Cheers,
Dawn.

[SH_94]

Dear @apathetichell ,

Thanks a lot for the prompt response.

May i know what the switch function works in this case?

Thank you.

[DawnDuong]

Hi @SH_94 

the switch function syntax is explained here:

https://help.alteryx.com/current/designer/conditional-functions

[apathetichell]

switch function takes in a value (in this case the format code "a"-"h")  and then provides a value/formula based upon that code (in this case different date/time parses)... Switch is key to simplifying complex nested if/then statements...