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?
Solved! Go to Solution.
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
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.
Hope that helps, please let me know if that worked for you.
Cheers,
Angelos
Hi
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
Hi @Adam_B,
I believe it would actually be:
yyyy Mon
(or yyyy Mon.)
so the month does have three characters like @AngelosPachis mentioned, but I'm pretty sure putting MMM expects there to be a two-digit month and the character "M".
Got it, that worked. However, now I have additional data within that field that has "2012 Q1" with multiple years and QTRS, so it would be "2013 Q2" or "2016 Q3" and so on. Can I use the aforementioned parse process, then a formula to resolve the QTR issue?
Thanks,
Adam
Hi @Adam_B,
Depending on what date you want to convert Q1-Q4 to, you can most likely accomplish this within a single formula tool. Find and replace combined with the datetime tool could also work, depending on the size of your data. Hope this helps!
Here is the formula I used:
However, the output has Null when the Date field ([Quarter]) is Month and Year (MAY 2018). Do I add another datetimeparse?
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]!
receiving this error:
ConvError: Formula (3): DATETIMEPARSE: Cannot convert "APR 2018" to a date/time with format "%Y %b": Expected a number for year: 'APR 2018'