Date Transformation

[samxhander09]

Hi Community! We had an exercise class to transform dates. I'm wondering what is the best way to approach this, the scenario is, the data Field 1 column can come in different format, so I placed multiple regex date time but I'm not sure it's the best way. Im attaching my work sample as well as the ultimate goal. hoping for your assistance, thank you in advance.

[Qiu]

@samxhander09 
Your sample input only has one format?

I usually will use the filter tool to seperate the data stream for different date format then apply the DateTime Tool for each data stream.
After, Union them back together.

Can you check your sample input?

[samxhander09]

@Qiu Yes for now it has only one format, but we can't tell when it would chhange hence the multiple regex date time

[oyes2704 · Accepted solution]

Hi @samxhandler09,

I would probably prefer to build cases within the formulas - especially since dates like '02/01/2023' are not clearly Feb01 or Jan02.

The formulas at least will give you priorities of cases to consider.

You can use GetWord () or Substr. I like GetWord and you need to replace "-" with " " space first.

Replace([Field1], '-', ' ')

For GetWord function remember, it counts from 0 up  and test it to be numeric 

if not(ToNumber(getWord(Field1,1)))>0 then DateTimeParse("%d-%m-%y") else DateTimeParse("%d-%b-%y") endif

If you are not sure month is used as 3 characters or longer, use LENGTH to see the Length of the month. 

if not(Length(getWord(Field1,1)))>3 then DateTimeParse("%d-%B-%y") else DateTimeParse("%d-%b-%y") endif

The formulas will not be dropping errors that DateTime Tool is throwing when the conversion wasn't applicable:

ConvError: DateTime (3): DateTime_Out3: Cannot convert "31-Oct-22" to a date/time with format "%d-%m-%Y" and language "English": Expected a number for Month: 'Oct-22'

If you prefer to parse values with different methods in separate fields like you did DateTime_Out2, DateTime_Out3, DateTime_Out4, there's a great new function coalesce that returns the first non-null value - should be Designer Desktop 2023.1 onwards:

COALESCE([DateTime_Out2], [DateTime_Out3], [DateTime_Out3])

In situations like above '02/01/23' are not clearly Feb01 or Jan02 - I would probably run Summary tool to identify range of values and then proceed from there.

I put some ideas back into your workflow, go ahead to mix and match to resolve your use case.

[Qiu]

@oyes2704 
Very detailed explanation! 🙌

[samxhander09]

Thank you for that explanation @oyes2704! I appreciate both of you