Hello,
How do I convert a date field organized as DDMONYYYY (i.e. 01JAN2015) into a Date format, please?
Thank you in advance.
Cheers
Solved! Go to 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
You can do this directly with the DateTimeParse function:
datetimeparse(Right('0'+[Text],9),'%d%b%Y')
I did forget about that function. Thanks.