Date Transformation

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.

DATE TRANSFORMATION EXERCISE.yxmd

Developer

Developer Tools

Date Time

Best Practices

Accepted answers

oyes2704

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.

DATE TRANSFORMATION EXERCISE.yxmd

All comments

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?

2023-12-20 134447.png

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

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'

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.

DATE TRANSFORMATION EXERCISE.yxmd

Qiu

@oyes2704
Very detailed explanation! 🙌

samxhander09

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

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235