REGEX and padding

[pcatterson]

I'm trying to learn REGEX and I was wondering its usefulness with dates.

I have some hypothetical dates I want to convert to Alteryx's Date format.  Currently I'm using this formula:

REGEX_replace([Field1],'(.*?)/(.*?)/(.*)','20$3-$1-$2')

I don't know how to pad the left of the day and month portions.  Is there a way?

Starting Date	Current Conversion	Desired Conversion
2/3/16	2016-2-3	2016-02-03
2/13/16	2016-2-13	2016-02-13
12/3/16	2016-12-3	2016-12-03
12/13/16	2016-12-13	2016-12-13

I'm sure that there are some great macros that convert dates, but i am only doing this to teach myself the how i can better use REGEX.

[RodL]

RegEx is really useful, but you might want to take a look at the DateTimeParse function that you can use in a Formula tool. If you go to Help for DateTime Functions, this can sometimes be easier. It can natively output the way you want it.

I keep playing with ways to use RegEx because I'm trying to continue to learn it as well, but figured I'd mention what is native to Alteryx as well.

[MarqueeCrew]

@pcatterson,

I'm sure that someone ( @RodL or @jdunkerley79 ) will come up with some awesome RegEx statement for you.  I've played with it and found another way to solve it (avoiding the use of date macros):

'20'+ getword(replace([date],'/',' '),2) + '-' +
padleft(getword(replace([date],'/',' '),0),2,"0") + '-' +
padleft(getword(replace([date],'/',' '),1),2,"0")

There are so many ways to solve problems that it is sometimes fun (in a geeky way) to show alternatives.  

Have a great weekend,

Mark

[RodL]

@MarqueeCrew,

You are making some rather incorrect assumptions if you think I would be one to come up with "awesome RegEx". What I can do only extends as far as how well a Google search works.

[jdunkerley79 · Accepted solution]

@MarqueeCrew challenge accepted ... but not necessarily successfully completed :)

Regex_replace('0' + Replace([Starting Date], '/', '/0'), '0*(\d\d)/0*(\d\d)/0*(\d\d)', '20$3-$2-$1')

This will do it in one fo(w|u)l sweep.

Basically it guarentees all numbers are at least 2 digits (by adding 0 always)

Then regex grabs the last two digits and moves around to required position as per OP.

[pcatterson · Accepted solution]

Thanks so much, thats the guts of what I need.

One small correction:

Regex_replace('0' + Replace([Starting Date], '/', '/0'), '0*(\d\d)/0*(\d\d)/0*(\d\d)', '20$3-$1-$2')

The month and day were reverse above in the earlier one.

[MarqueeCrew]

@jdunkerley79,

I like it.  A minor point, but unlike the English we express dates here in the format of month before day.  I see your attempt to re-colonize us and reject the offer.

By the way, I was informed at inspire that the community was creating a "no Frisch" Friday policy.  I am apologizing within this thread for my inability to resist answering questions (even when regex isn't my specialty).

Cheers,

Mark

[RodL]

Eh...I don't think that "no Frisch Friday" policy was voted on by the community, so for my two cents, I'll take your input ANY day of the week! 

[jdunkerley79]

Sorry yes am British so days first in my head, though as I am very strange tend to think in YYYYMMDD most of the time.

@MarqueeCrew: If you having Fridays off then in the spirit of fair competition should do the same