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

  • Does the app maintain the field or just query it? If the latter, you could add a new timestamp to replace this column and recreate the column LastUPDATE as a virtual column. – Colin 't Hart Aug 18 '14 at 17:44

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)
  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
  • Thank you Balazs Papp. That worked. I appreciate your answer and helping me out. I need to get use to working with dates more. The dash between date and time plus the ff3 miliseconds was throwing me off – Erin Aug 18 '14 at 17:16
  • How would i do 800 records with different date and time? instead of the (select '2012/09/06-21:17:30:019' .......) – Erin Aug 18 '14 at 17:20
  • I worked with it and placed the lastupdate column name in the select statement (select lastupdate as lastupdate from dual) and got my answer.. Thanks again for your support!!! – Erin Aug 18 '14 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.