We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

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

Month Year String to Date?

TheRhino
6 - Meteoroid

I have two columns of data with date information - one Double, with year, and another Double, with Month

 

I have created another column with Month in String format so I have that as well

 

I've been able to create a combined String column by converting the Year into String and using the expression [Month]+"-"+[String Year]

 

I cannot figure out how to convert this to a date field using the DateTimeParse formula

 

Is there another combination of double/string values I should be using? What other conversions do I need to do?

 

I've read every help page of time/date to no avail!

Thanks!

18 REPLIES 18
MarqueeCrew
20 - Arcturus
20 - Arcturus
Did you try formatting it as: YYYY-MM-DD? A string in that format is ready for conversion to DATE.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
TonyM
Alteryx
Alteryx

If the year format is in yyyy form you can change your expression to [Month]+", "+[Year] (note the space after the comma) and then use the DateTime tool to format your string into a date. The DateTime tool can convert "Month, yyyy" format into a Date.

 

TheRhino
6 - Meteoroid

So if I am understanding you correctly, I should join the two string column (Year and Month) such that it reads "YYYY-MM" - but what is the DateTimeParse output I should use? Can you walk me through that formulation? THank you!

RodL
Alteryx Alumni (Retired)

You haven't said anything about the day of the month, but as @MarqueeCrew said, if you format it as YYYY-MM-DD, then that can just be converted to a date. You could just add a '-01' to your year/month concatenation to supply the day if you don't have one.

If you only have the month and year, I would just use a Formula tool with this expression...DateTimeParse([ConcatDate],'%m-%Y')...and it will default to the first day of the month. 

If you go to the DateTime Function Help...http://help.alteryx.com/10.0/index.htm#Reference/DateTimeFunctions.htm...scroll to the bottom and you will a large list of specifiers that you can use to configure just about any date (and you are restricted by the exact punctuation that you sometimes have using the DateTime Parsing tool.

TheRhino
6 - Meteoroid

OK - so I have a string field in format [Month], [Year] (ex: April-2008)

 

Capture.PNG

 

When I try to create a new field with DateTimeParse([Month Year String], b%, Y%) I get an error

 

 

RodL
Alteryx Alumni (Retired)

You need quotes around 'b%, Y%'.

RodL
Alteryx Alumni (Retired)

And it should be '%B, %Y' since it is the full month name (and sorry, didn't notice you had the % sign in the wrong place).

TheRhino
6 - Meteoroid

Sorry to be a pain....

 

Now I am getting this with your recommendation DateTimeParse([Month Year String],'b%, Y%') 

 

Capture.PNG

 

The field type should be Date, correct?

RodL
Alteryx Alumni (Retired)

No worries...look at my previous post that I added...you have % sign in the wrong place...

 

DateTimeParse([Month Year String],'%B, %Y')

Labels