Converting date field and receiving error

Hello,

I have a date field: YYYY MM

and I want to convert it to YYYY-MM-DD however, I am receiving the error:

ConvError: DateTime (6): NewDate: Cannot convert "2017 MAY" to a date/time with format "%Y-%m-%d": Expected separator '-%m-%d', got: 'MAY' Record #3

when using the DateTime Parse item choosing the yyyy-MM-dd option. Am I missing a step?

Topic of Interest

Accepted answers

clmc9601

Hi @Adam_B,

Yes, add another formula node at the bottom. In that node, update [New Date] with an expression like this:

IF IsNull([New Date])
THEN Datetimeparse([Quarter], "%b %Y")
ELSE [New Date]
ENDIF

It will hopefully fix the rest of the nulls in [New Date]!

clmc9601

Hi @Adam_B,

Yes, those warnings are because you have two different formats. I'm guessing you got warnings but no nulls as a result? Because that last formula node you just added will fix the nulls. If you want to clear the errors, wrap the first datetimeparse in this IF statement. Actually, you can probably combine both formula nodes into this single IF statement.

IF regex_countmatches(Left([Quarter],1), "\d")>0
THEN datetimeparse([Quarter],"%Y %b")
ELSE datetimeparse([Quarter], "%b %Y")
ENDIF

It checks to see if the first character in [Quarter] is a number. If it's a number, then parse YYYY Mon, and if not, then it parses as Mon YYYY. Hope this helps!

All comments

TrevorS

Hello @Adam_B
Can you please share your workflow and some sample data?

It would help to see what data format the DateTime tool is looking at, and how the DateTime tool is configured.

Thanks!
TrevorS

AngelosPachis

Hi @Adam_B .

The error is probably because your [Quarter] field has a year month format (so 2017 Jan, month with 3 characters) and the Datetime tool is configured to look for a year-month-day format (2017-01-01, month with numerical values).

To configure the Datetime tool properly and look for the month with 3 letters, select the custom format option and use

yyyy MMM

as the expression.

Screenshot 2021-02-24 183847.jpg

Hope that helps, please let me know if that worked for you.

Cheers,

Angelos

example.yxmd

Adam_B

I am getting this error still:

ConvError: DateTime (6): NewDate: Cannot convert "2017 JAN" to a date/time with format "%Y %mM": Expected a number for Month: 'JAN' Record #1

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703