Home » SQL & PL/SQL » SQL & PL/SQL » Convert to numberic/date (Oracle)
Convert to numberic/date [message #576011] Wed, 30 January 2013 10:46 Go to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Hi guys,

I currently have a table with a VARCHAR column which is used to store notes. Currently the notes read something like 'Verified 01/01/2012'. I am trying to convert it to a date column so I can run reports using the date (select between dates etc).

I have tried with the substr function but since the records are all different doesn't really work. (plus that doesn't make it a date so not sure it would work for searching).

Does anyone have any opnions or advice on how to proceed?

Much appreciated

Steve
Re: Convert to numberic/date [message #576015 is a reply to message #576011] Wed, 30 January 2013 11:03 Go to previous messageGo to next message
BlackSwan
Messages: 23059
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

TO_DATE() function is used to convert string to DATE datatype.
Re: Convert to numberic/date [message #576016 is a reply to message #576011] Wed, 30 January 2013 11:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2064
Registered: January 2010
Senior Member
Assuming all dates, if present, are in mm/dd/yyyy format:

TO_DATE(REGEXP_SUBSTR(notes,'\d\d/\d\d/\d\d\d\d'),'mm/dd/yyyy')


Notes without substrings in '\d\d/\d\d/\d\d\d\d' (\d represents a digit) format will be converted to NULL dates.

SY.
Re: Convert to numberic/date [message #576107 is a reply to message #576016] Thu, 31 January 2013 09:01 Go to previous message
SteveShephard
Messages: 34
Registered: August 2012
Member
Thanks Solomon, that has done the trick Very Happy
Previous Topic: Need help in creating a job.
Next Topic: Conditional Sequence usage
Goto Forum:
  


Current Time: Wed Nov 26 15:45:13 CST 2014

Total time taken to generate the page: 0.13172 seconds