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, 

[cpet13]

@CDIns I have done quite a bit with dates and date formats in Alteryx. One thing I have found is that if I am using an input data tool that is connected to a database and I am bringing in a date field, I usually have to write a SELECT statement that casts the date field as a date in ordre to get the field into the date format Alteryx uses; SQL's date format, 8/15/2024, is not the Alteryx date format, 2024-08-15, so is cast as something else (a vstring) unless the CAST(x AS Date) is used in the select statement.

If you just try to change the format of 8/15/2024 to a date data type using a select tool or a formula, Alteryx will not recognize that as a date and so will return NULL.

There is a tool called DateTime that you can use to specify the format of the date field coming in, which will convert it to the datetime that Alteryx can work with and recognizes (see screenshot).

[binuacs]

@CDIns if the date is in YYYY-MM-DD format then it should work with the DateTimeFormat() function

[OTrieger]

There is a rule, if you finding yourself not able to apply or implement something that you learned, then there is something that you are still not fully understanding about it. A Person's doingness is entirely depending on his full understanding of the subject. If you really would like to master it, no matter which advice you will get here, my suggestion for you is to go back to the academy lessons on dates and learn them again so you will know it and understand how to use it the next time.

One tool to do it as explained above, Date Parse tool, another tool will be Formula tool. 

[CDIns]

Thanks,  @binuacs. In another situation, I need to append MMDD of transaction date to the end of my transaction #. My transaction date is currently a V_String in 08/16/2024. To change this would I use the following formula:

Tostring([Transaction#])+right(Datetimeformat([TransactionDate]),'%m%d%y'),4)?

Having issues here as well. 

thank you for all your help. 

[OTrieger]

You can find all your answers here: https://help.alteryx.com/current/en/designer/functions/datetime-functions.html#idp376442 

[cjaneczko]

You can use the following.

tostring([Transaction#]) + tostring(datetimeformat(DateTimeParse([TransactionDate],'%m/%d/%Y'),'%m%d'))

[CDIns]

Thanks, @cjaneczko . When my date imports as INT32 data type and reads as 20240731, how would I convert this to 07/31/24? Not having any luck with datetime tool or datetimeformat formula.

[apathetichell]

@cpet13 - "sql" native date format tends to be yyyy-mm-dd. Alteryx uses this. Which DB are you using which does not support that? 

[apathetichell]

@CDIns datetimeparse(tostring(20240731),"%Y%m%d")