varchar2 date type
We have a table with a column type varchar2 and my customer is storing a DATE into that column. The table is based on a Oracle Form and there is a calendar attached to the front-end. The Form Users select/enter and save date without any issues. The date store into the table in a YYYY/MM/DD 00:00:00 format and display from the front-end as DD-MON-YYYY. (so far so good).
Recently, I designed a PL SQL package interface and have a DBlink with another oracle based system. The DBlink table has a column with type varchar2 and store the date into the following format: YYYYMMDD. I need to transform the source column date and populate into the target tables. I have tried and used different date formats but no luck. how can i achieve my goal. Please help to resolve the issue. Thanks in advance!.
Source (DBLink external table)
Column type varchar2 and date format is YYYYMMDD
Column type varchar2 and date format YYYY/MM/DD 00:00:00 and display as DD-MON-YYYY.
select substr(yourcolum,1,4) || '/' '' substr(yourcolumn,5,2) || '/' || substr(yourcolumn,7,2) || ' 00:00:00'
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Originally Posted by PAVB
Pablo, It works perfect!.
Thank you so much for the help.
Click Here to Expand Forum to Full Width