Date Time Parse Question

[DarrellB]

Hi Community,

I'm a newbie to Alteryx and was looking for some help in understanding DateTimeParse.

I've used the following formula

DateTimeParse(
ToString([DATES_DATE_MONTH]) + ' '
+ PadLeft(ToString([NEW_DATES_DAY2]), 2, '0') + ' '
+ ToString([DATES_DATE_YEAR])
, '%m %d %Y')

Which is working rather well except for instances where ;

[DATES_DATE_MONTH] is null

[NEW_DATES_DAY2] is 1 (1 is a substitute vale for null - updated in a upstream step in the flow)

[DATES_DATE_YEAR] is 2016 etc.

This produces a result of 2016-01-20.

Any ideas why this is the case?

Many thanks

Darrell

[Thableaus]

Hi @DarrellB

Could you please post a sample of your data (or just the data with the fields involved in this calculation)?

Thanks!

Cheers,

[DarrellB]

Hi thanks for your reply, here's some sample data. You'll see the ones with a valid month number are fine, the null values seem to be the problem.

[Thableaus · Accepted solution]

Hi @DarrellB

I'm pretty sure this happens because the Null value is not a string, therefore, when using the parameters (%m %d % %Y), it messes everything up.

When you just put your formula without the DateParse function

ToString([DATES_DATE_MONTH]) + ' '
+ PadLeft(ToString([NEW_DATES_DAY2]), 2, '0') + ' '
+ ToString([DATES_DATE_YEAR])

The Null lines become "01 2016", because there's no ToString for a Month.

This means it will read -> Month = 01, day = 20, year = 16.

I changed your formula a bit to fit this case

DateTimeParse(
iif(IsNull([DATES_DATE_MONTH]), "01", ToString([DATES_DATE_MONTH])) + ' ' + PadLeft(ToString([NEW_DATES_DAY2]), 2, '0') + ' ' + ToString([DATES_DATE_YEAR]) , '%m %d %Y')

It works fine this way.

Cheers,

[DarrellB · Accepted solution]

Excellent, thank you very much !