DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: varchar2 date type

  1. #1
    Join Date
    Aug 2013
    Posts
    4

    varchar2 date type

    Hi,

    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
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
    Posts
    4
    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