CODEWITHSUNDEEP
regexoracle-database
Benjamin Kuntz
Benjamin Kuntz

Reputation: 11

How to get datetime from oracle varchar with surrounding characters

I have a varchar2 'Printed 4/21/2014 3:00:00 AM' and want to get just the '4/21/2014 3:00:00 AM' as a date. My thought was to use something like:

I have: REGEXP_SUBSTR('Printed 4/21/2014 3:00:00 AM', '[0-9/ :APM]*$') Results in: 4/21/2014 3:00:00 AM

However,

SELECT to_date(REGEXP_SUBSTR('Printed 4/21/2014 3:00:00 AM', '[0-9/ :APM]*$'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

results in an 'Invalid Month' error.

Suggestions?

Upvotes: 0

Views: 63

Answers (2)

Alex Poole
Alex Poole

Reputation: 191255

If the format is fixed - including the text you want to ignore - then you could convert the whole string to a date or timestamp, without needing to use a regular expression or substring:

select to_date('Printed 4/21/2014 3:00:00 AM', '"Printed" MM/DD/YYYY HH:MI:SS AM') as result
from dual;

RESULT             
-------------------
2014-04-21 03:00:00

The fixed 'Printed' part is treated as a character literal by enclosing that part in double quotes inside the format model.

If you really aren't interested in the time then you can remove that afterwards:

select trunc(to_date('Printed 4/21/2014 3:00:00 AM', '"Printed" MM/DD/YYYY HH:MI:SS AM')) as result
from dual;

RESULT             
-------------------
2014-04-21 00:00:00

Upvotes: 0

Benjamin Kuntz
Benjamin Kuntz

Reputation: 11

Issue: Correctly identified by mathguy - date format

I decided I don't really need the timestamp, thus:

SELECT to_date(REGEXP_SUBSTR('Printed 4/21/2014', '[0-9/]*$'), 'MM/DD/YYYY') FROM DUAL;

works fine.

Upvotes: 1

Related Questions