Start a new topic

Using regular expressions to convert date format

Hi!

Is it possible to use regular expressions to convert a long date to a short date?

We need to change the date

from 22-Jun-87

to 22/06/1987

I see lots of examples of changing numeric format to other numeric formats but not this. Thanks for any help with this.

Regards

Christine

 

 


Christine,

Converting the month to a number is fairly easy. Adding 19 or 20 to the year is more problematic. If that part isn't necessary (RE will be able to figure out the proper century most of the time), then you can easily do this.

Create an entry for each month like below and mark all of them as RegEx.

Replacement Value: /06/
Value to match on: -Jun-
Hi--I'm having the same issue as Christine, and I set this up. But my date of 08-Jul-2013 is now coming through as 08/07/2013, which in this country, doesn't work for me in my batch. Do you have any suggestions for putting it in the US format now?

PLUS, my date field has a time stamp. 08-Aug-2013 02:36:23 AM PDT Any thoughts on that besides splitting the field before my import?

(I was so happy to be able to search to find this--nice forum bonus!)

Any help you can provide will be appreciated! Karen
While this is more complicated, it can still be done all through dictionaries. After your entries for each month you will want another entry to remove the time stamp. Then another to swap the month and day.

Remove Time Stamp
Replacement Value: Blank
Value to Match on: (?
Swap month and day
Replacement Value: $2/$1/$3
Value to Match on: (^\d+)/((?
THANK YOU.

Hi--I'm still unable to make this work.  Would you mind looking at what I have? It's successfully getting rid of the time stamp part, but the date is still coming through as DD/MM/YYYY.  Any suggestions?   Here's what I'm trying to convert and my dictionary is copied below it: 

29-Sep-2013 02:19:04 AM PDT

 

/07/ -Jul- TRUE
/1/ -Jan- TRUE
/2/ -Feb- TRUE
/3/ -Mar- TRUE
/4/ -Apr- TRUE
/5/ -May- TRUE
/6/ -Jun- TRUE
/8/ -Aug- TRUE
/9/ -Sep- TRUE
/10/ -Oct- TRUE
/11/ -Nov- TRUE
/12/ -Dec- TRUE
(? TRUE
$2/$1/$3 (^\d+)/((? TRUE

 

Karen,

Looks like a space was left after the date when it stripped out the time stamp. After adjusting the regex to strip out the time stamp it works.

Here is the modified regex for the time stamp: (?
Woot woot. Thank you!!!! This is awesome.

I get the error message "Error parsing regular expression...parsing "(^\d+)/((?" - Unrecongized grouping construct."


What am I doing wrong?

Sandy--which part are you trying to work on?  Swapping months and days?  (To change, say 17/05/2017 to 05/17/2017?  If that's it, the code in my dictionary is actually this: (^\d+)/((?<=/)\d+(?=/))/(\d{4}$)


Matches to  $2/$1/$3 with the Use Regular Expressions clicked. I have no idea what it means.  Just doing a copy/paste. 

Hi Sandy,


The example listed above doesn't look quite right.  I'm wondering if something got messed up when converting the data over to the new forum system.  Can you give me a sample of the data you are trying to convert and I can give you the proper syntax.


Thanks,


John

Login or Signup to post a comment