Convert a date

[zemariajudice]

Hello,

How do I convert a date field organized as DDMONYYYY (i.e. 01JAN2015) into a Date format, please?

Thank you in advance.

Cheers

[MarqueeCrew · Accepted solution]

Two tips:

1. Use a regular expression to swap fields around and to add '-' dashes.  04SEP2016 becomes 2016-SEP-04

2. Use a find and replace to swap 09 for SEP so that 2016-SEP-04 becomes 2016-09-04

After that you can convert this string to a date.

I allowed for a 9 or 8 byte input (no error checking or defaults used).

IF LENGTH([Date Field])==9 
THEN REGEX_REPLACE([Date Field],"(\d{2})(\w{3})(\d{4})","$3-$2-$1")
ELSE
REGEX_REPLACE('0'+[Date Field],"(\d{2})(\w{3})(\d{4})","$3-$2-$1")
ENDIF

[jdunkerley79]

You can do this directly with the DateTimeParse function:

datetimeparse(Right('0'+[Text],9),'%d%b%Y')

[MarqueeCrew]

I did forget about that function. Thanks.