Oracle 11g varchar2 datatype with date and time as YYYY/MM/DD-HH24:MI:SS:FF3

Question

I am working with a column 'LastUPDATE' in a database that is datatype of varchar2. This column is listed as YYYY/MM/DD-HH24:MI:SS:FF3 format. Example: '2012/09/06-21:17:30:019'

My first issue is that I cannot change the database or change this format due to the application that usses the data in this date and time format.

I need to know how I can work with only the date part, extracting it so that I can calulate the date from 30 days and so on.

I have been able to extract for the view by RegExpections but not able to get the data for calculation.

I am familar with Oracle because i have to work with the data in my work but not god at the date and time manipulation.

i have searched this site and others with no success on the same issue.

Can an Oracle person help me out. Oracle 11g Thank you E

Answer 1

First you need to convert it to TIMESTAMP datatype (because DATE type does not handle fractions of second). Then you can truncate it to date for example:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

with data as (select '2012/09/06-21:17:30:019' as lastupdate from dual)
select 
  trunc(to_timestamp(lastupdate, 'YYYY/MM/DD-HH24:MI:SS:FF3')) date_only,
  trunc(to_timestamp(lastupdate, 'YYYY/MM/DD-HH24:MI:SS:FF3')) + 30 date_only_30
from data;

DATE_ONLY           DATE_ONLY_30
------------------- -------------------
2012-09-06 00:00:00 2012-10-06 00:00:00