Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Converting date field and receiving error

Adam_B
8 - Asteroid

Hello, 

 

I have a date field: YYYY MM

Adam_B_0-1614184305225.png

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? 

 

10 REPLIES 10
TrevorS
Alteryx Alumni (Retired)

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

Community Moderator
AngelosPachis
16 - Nebula

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

 

 

Adam_B
8 - Asteroid

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

 

clmc9601
13 - Pulsar
13 - Pulsar

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".

Adam_B
8 - Asteroid

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 

clmc9601
13 - Pulsar
13 - Pulsar

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!

 

 Screen Shot 2021-02-25 at 8.47.40 AM.png

Adam_B
8 - Asteroid

Here is the formula I used:

Adam_B_0-1614274736292.png

However, the output has Null when the Date field ([Quarter]) is Month and Year (MAY 2018).  Do I add another datetimeparse? 

clmc9601
13 - Pulsar
13 - Pulsar

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]!

Adam_B
8 - Asteroid

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'

Labels