DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2013

    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

    Target table
    Column type varchar2 and date format YYYY/MM/DD 00:00:00 and display as DD-MON-YYYY.

  2. #2
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    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.

  3. #3
    Join Date
    Aug 2013
    Quote Originally Posted by PAVB View Post
    select substr(yourcolum,1,4) || '/' '' substr(yourcolumn,5,2) || '/' || substr(yourcolumn,7,2) || ' 00:00:00'

    Pablo, It works perfect!.
    Thank you so much for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.