Month Year String to Date?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
OK - so I have a string field in format [Month], [Year] (ex: April-2008)
When I try to create a new field with DateTimeParse([Month Year String], b%, Y%) I get an error
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You need quotes around 'b%, Y%'.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry to be a pain....
Now I am getting this with your recommendation DateTimeParse([Month Year String],'b%, Y%')
The field type should be Date, correct?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
No worries...look at my previous post that I added...you have % sign in the wrong place...
DateTimeParse([Month Year String],'%B, %Y')
