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

Date parse method for DDMMMYYYY ?

ewhulbert
5 - Atom

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.    

6 REPLIES 6
jdunkerley79
ACE Emeritus
ACE Emeritus

Use %Y rather than %y. 

The former is for four digit years the later for two

MarqueeCrew
20 - Arcturus
20 - Arcturus

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
s_pichaipillai
12 - Quasar

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
5 - Atom

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

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.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
jdunkerley79
ACE Emeritus
ACE Emeritus

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

Labels