Varchar2 column with a date and time listed as YYYY/MM/DD-HH24:MI:SSFF3

I am more of a beginner in Oracle yet I do some querying on my job. I need to calculate the date only out of this date time varchar2 format(YYYY/MM/DD-HH24:MI:SSFF3). I was able to look at the date only using Regular Expresion but not sure how to pull the date out of this column so that I can do some calculations. The date/time of this varchar2 cannot be changed to a better format.

Is there a way to take the date and place it into another variable to do my calculations. Why they put a 'dash' between the 2 is based on the application they use. I hate dealing with dates in Oracle. This one is a new one to me.
Any help, hints would be appreciated.
Example of the calculations is lastupdate greater than 90days.

Comments for Varchar2 column with a date and time listed as YYYY/MM/DD-HH24:MI:SSFF3

Average Rating starstarstarstarstar

Click here to add your own comments

Aug 18, 2014
Rating
starstarstarstarstar
How to extract a date from a character string
by: Anonymous

You can convert a varchar2 column to date just by using the to_date function in SQL and providing the format of the date in the charcter string. In this case therefore the SQL statement would be like this:

SQL> SELECT to_date(substr(my_date_col,1,10),'YYYY/MM/DD') FROM my_table;

If you want to display it in a particular format you will need to wrap a to_char function around the to_date function and supply the relevant format string.

SQL> SELECT to_char(to_date(substr(my_date_col,1,10),'YYYY/MM/DD'),'DD-MON-YYYY') FROM my_table;

Alternatively you can issue an alter session statement to change the date format like this:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';

That is just for displaying the date though. Once you have extracted the date as an Oracle date you can manipulate it using standard date arithmetic.

See the Oracle Database SQL Language Reference for more information on working with dates in SQL.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.