Why is it so hard to convert date formats?

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,

Date Time

Common Use Cases

Best Practices

Data Investigation

Accepted answers

All comments

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). Screenshot 2024-08-15 073343.png

binu_acs

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

DateConversion_15082024.yxmd

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, @binu_acs. 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")

binu_acs

@CDIns

DateConversion_15082024.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703