Converting date field and receiving error

[Adam_B]

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? 

[clmc9601 · Accepted solution]

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!