Date parse method for DDMMMYYYY ?

[ewhulbert]

Good Afternoon, I have a question about converting a vstring to a date format.  The data I have comes from a massive .csv file that when I import everything comes in as a vstring.  I have a date field with comes in as a vstring DDMMMYYYY, aka "31MAR2016".  I want to convert that to something I can use as a date.  I have done the following which seems to work, but I would like to know if there are potential errors in this approach and if there is an easier and more straightforward way to do it.

my current method:

NewDate = DateTimeParse([OldDate], %d%b%y) which returns dates that for some reason are exactly 5 years too large, e.g. 31MAR2015 would come in as 2020-03-31.  To correct this I use:

NewDate = DateTimeAdd(NewDate, -5, "years")

Is there any easier way?  Are there errors this might pass along?  I didnt see any, but my data set is much too large to check each one.    

[jdunkerley79 · Accepted solution]

Use %Y rather than %y. 

The former is for four digit years the later for two

[MarqueeCrew · Accepted solution]

you are SOOOOOO close.....

your formula:  datetimeparse([date],"%d%b%y")

the lowercase y in your formula is read as 2020.

my formula:  datetimeparse([date],"%d%b%Y")

[s_pichaipillai]

ooops. i saw Mark and James responded to the same :)

the issue here is %y instead %Y

try datetimeparse([Date],'%d%b%Y'), it should work :)

[ewhulbert]

Thanks everyone for the incredibly fast help, I didnt realize the Y was case sensitive. 

[MarqueeCrew]

http://help.alteryx.com/10.1/index.htm#Reference/DateTimeFunctions.htm?Highlight=datetimeparse

Help has been a long friend of mine.  I find that if it seems like it could be easier, usually the problem is me.

[jdunkerley79]

Agree entirely with if it seems like should be easier is me.

Though think Date Parsing one of Alteryx's very few weaknesses - was what originally inspired me to try making a custom tool. If you fancy trying the result, it is on GitHub and just needs the field set in config to parse most formats of dates (including this one).