Problem with date format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Problem with date format

  1. #1
    Join Date
    Jan 2010
    Posts
    20

    Problem with date format

    I have table with a column defined like
    INSTATE_DATE DATE
    I have to laod the data frm the csv file to this table. my csv file has data 00/00/0000. Even though it is an invalid date
    I have load this date to my oracle table. I have an option of loading a default value say 01/01/2010 everytime i come across 00/00/0000.
    I tried to modify the control file like
    INSTATE_DATE DATE "replace(:INSTATE_DATE,'00/00/0000','01/01/2010')"
    Even then im getting the error "ORA-01821: date format not recognized"
    Please let me know if there are any other way to achive this.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Try...
    Code:
    INSTATE_DATE "decode(:INSTATE_DATE,'00/00/0000','01/01/2010',:INSTATE_DATE)"
    ... if still having datatype issues add to_date() function.
    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
    Oct 2009
    Location
    Banglore
    Posts
    8
    select
    ( case
    :INSTATE_DATE when to_date('00/00/0000','DD-MM-YYYY') then
    return to_date('01/01/2010','DD-MM-YYYY');
    else
    return :INSTATE_DATE
    end ) INSTATE_DATE
    from table1

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by aryasen View Post
    select
    ( case
    :INSTATE_DATE when to_date('00/00/0000','DD-MM-YYYY') then
    return to_date('01/01/2010','DD-MM-YYYY');
    else
    return :INSTATE_DATE
    end ) INSTATE_DATE
    from table1
    You didn't test it, don't you?
    look...

    Code:
    SQL> 
    SQL> select to_date('00/00/0000','DD-MM-YYYY') from dual;
    select to_date('00/00/0000','DD-MM-YYYY') from dual
                   *
    ERROR at line 1:
    ORA-01847: day of month must be between 1 and last day of month
    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.

  5. #5
    Join Date
    Jan 2010
    Posts
    20
    I tried to_date also.. still its not working..
    Im not sure whether im using to_date function properly.. please correct me if im wrong

    INSTATE_DATE DATE "to_date (replace (replace (:REINSTATE_DATE, '/', ''), ' ', ''), 'mmddyyyy')"

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Use decode() - look at second post.
    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.

  7. #7
    Join Date
    Jan 2010
    Posts
    20
    Thanks. Its working fine with boundfiller solution.

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