Why is it so hard to convert date formats?

[CDIns]

I always run into this issue converting date formats. Maybe im not understanding something. 

My date field is importing to alteryx as int36 data type as YYYY-MM-DD. First I tried to add a formula on to convert it using datetimeformat([Date],"%m/%d/%y") and the format doesn't change. 

Then i tried to add select tool, change to a string format and then use Datetime tool. Selected string to date/time format and selected the format the date is current in yyyy-MM-dd but now how do i chose what format to convert it to? 

I simply want to change 2024-07-31 to 07/31/2024. 

Please help. 

Thank you, 

[binuacs]

@CDIns 

[CDIns]

Thanks @apathetichell . I also have dates importing as INT32 in the 07/31/2024 but the below formula isnt converted them to 2024-07-31. Is there a separate formula needed for this? 

[apathetichell]

Hey - your problem isn't Alteryx dates - your problem is date formats in your source document are intermingled. So in your case- you should do something like

if left(tostring([datefield]),1) in ("0","1") then datetimeparse([datefield],"%m%d%Y") else datetimeparse([datefield],"%Y%m%d") endif -

if you have both m/d/Y and d/m/Y - that's stupid. It's a data issue upstream. fix it there. 

[cpet13]

@apathetichell I realize the issue is that that format is how data for my organization is being stored in SQL; that is not the default date format for SQL. Thank you for bringing this up.