Date Time Format with REGEX

[RCern]

I have a data set with over 10 date columns that are string formatted like "20240801".  I want to use REGEX to format and convert the column to a date field and format it as "8/1/2024".  I don't want to use the DateTime Convert tool and formula  tools for each column since it will make my workflow very cumbersome.  Is there a way to use a regex tool for each column instead without having to use additional parse and formula tools?

[OllieClarke]

@RCern the least cumbersome way is to use a multi-field formula tool.

Select the 10 'date' fields, and then you can parse them all in one fell swoop

//Format as string
DateTimeFormat(
//Parse from string to date (YYYYMMDD)
(DateTimeParse([_currentfield_],'%Y%m%d'))
//Format of outgoing string
,'%m/%d/%Y')

 
There's more info on the datetime functions here: https://help.alteryx.com/current/en/designer/functions/datetime-functions.html#idp376621 (in case you wanted d/m/y instead of m/d/y)

and the multi-field formula tool here: https://help.alteryx.com/current/en/designer/tools/preparation/multi-field-formula-tool.html

Hope that helps,

Ollie

[DataNath]

Hey @RCern, there's no need to use multiple of either the DateTime tool or standard Formula tools - you can just convert all fields at once in the Multi-Field Formula like so with the standard DateTimeParse() + DateTimeFormat() approach:

I've assumed that your input date is YYYYMMDD and the output is MM/DD/YYYY here - just update the expression as needed if this isn't correct:

DateTimeFormat(DateTimeParse([_CurrentField_],'%Y%m%d'),'%m/%d/%Y')

Also, if you just want to overwrite the existing fields rather than create new ones, simply untick 'Copy Output Fields and Add'.

Finally, from your original post - you cannot have the output field in a date format with something like 8/1/2024. Alteryx can only store dates in ISO format which is YYYY-MM-DD.

[RCern]

@DataNath First of all, amazing!  Thank you!   It's not quite working as easily.  I'm pulling data from an Oracle DB.  The date columns are 8 digit "Characters" in the database and pull into Alteryx (via the In-DB tool) as "STRING" data types.  When I use the MuliLine tool, it works fine, but cuts off the last two digits of the year.  See screenshots below.  What would cause that?

 

  

[DataNath · Accepted solution]

Hey @RCern, no problem! As you can see in your final image, the current size of the field in question is only 8, meaning it'll truncate any characters after this. Whereas, to convert to a YYYY-MM-DD, this is 10 characters. You can either increase the size of this field before the Multi-Field Formula tool in a Select tool, or edit the size of the outgoing field in the Multi-Field Formula itself with this config option (just tick it and increase the size):

[RCern]

@DataNath   Awesome it worked!  Thank you!!  Can I ask another question or two?

I have another workflow where there are four date columns from the Oracle Database.  Two are formatted in the database as "Date" type (Length 4) and the other two are formatted as "Timestamp" type (Length10). 

The first two come through Alteryx as YYYY-MM-DD (DATE Size 10) and the other two columns come through as YYYY-MM-DD HH:MM: SS (DATETIME Size 10)

Can I still use one multi-Field Tool to date format both columns as MM/DD/YYYY even though their original formats are different?  If so, how would I configure the tool?   I tried myself just using DatetimeFormat([_CurrentField_],'%m/%d/%Y') but I get a "Type Mismatch.  Number provided where a string is required."   I don't understand this because the fields selected are Dates.

 

 

 

[DataNath]

Hey @RCern - I have that 'error' in my workflow as well, I think it's a result of using "All Types of" fields. If it's bothering you, you can change this drop down to Text or DateTime etc, or wrap the field in ToString() like:

DatetimeFormat(ToString([_CurrentField_]),'%m/%d/%Y')

[RCern]

@DataNath Hmm, I did that and the column becomes 100% null and I get multiple Multifield Formula errors that say "EFF_DT:  "10/01/1992" is not a valid Date".  Please see two screenshots below.  Is the date not truly a date coming into Alteryx even though it says so in the Select tool?

  

[DataNath]

Ah @RCern, because you're formatting it into a custom date format (mm/dd/yyyy) which Alteryx cannot store as a date, it'll null. You'll have to use the extra configuration to change the outgoing data type to a string.

[RCern]

@DataNath What do you mean by extra configuration?  Another tool?  Also, how is this different than what we did earlier today with the other columns?