Results 1 to 5 of 5

Thread: to_date problem

  1. #1
    Join Date
    Nov 2002
    Posts
    42

    Unanswered: to_date problem

    If I run the following:

    select to_date('5/28/2006 12:00:00 AM','MM/DD/YYYY HH:MIS AM') from dual

    I get NO errors.

    If I run the following:

    select pd_finish,to_date(pd_finish,'MM/DD/YYYY HH:MIS AM') from tbl

    I DO get errors. The hard-coded date in the first statement is in the exact format as the dates from the table in the second statement, in fact that date was copied out of the table.

    Why does this work when running the first statement, but not the second? The error code is: ORA-01843

    What I am trying to do is calculate the number of days difference between pd_finish (date) and sysdate.

    Thanks...

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >select pd_finish,to_date(pd_finish,'MM/DD/YYYY HH:MIS AM') from tbl
    select pd_finish,TO_CHAR(pd_finish,'MM/DD/YYYY HH:MIS AM') from tbl;
    I suspect mine will work better than yours.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Code:
    rbaraer@Ora10g> create table tbl(pd_finish varchar2(30));
    
    Table created.
    
    rbaraer@Ora10g> insert into tbl(pd_finish) values ('5/28/2006 12:00:00 AM');
    
    1 row created.
    
    rbaraer@Ora10g> select pd_finish,to_date(pd_finish,'MM/DD/YYYY HH:MI:SS AM') from tbl;
    
    PD_FINISH                      TO_DATE(P
    ------------------------------ ---------
    5/28/2006 12:00:00 AM          28-MAY-06
    
    rbaraer@Ora10g>
    Absolutely no problem... the value must not be the one you gave .

    BTW storing a date as a varchar2 is a BIG design flaw.

    ALWAYS ALWAYS ALWAYS STORE DATES AS DATES !

    Otherwise, you will continously get converting errors along the way, and you will be the only one to blame for it .

    Regards,

    rbaraer

    Edit : as for the nb of days beween pd_finish and sysdate :
    Code:
    rbaraer@Ora10g>  select trunc(sysdate - to_date(pd_finish,'MM/DD/YYYY HH:MI:SS AM')) NB_OF_DAYS from tbl;
    
    NB_OF_DAYS
    ----------
            58
    
    rbaraer@Ora10g>
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Sep 2004
    Posts
    60

    Lightbulb

    select to_date('5/28/2006 12:00:00 AM','MM/DD/YYYY HH:MIS AM') from dual
    ---'5/28/2006 12:00:00 AM' is taken as Character

    select pd_finish,to_date(pd_finish,'MM/DD/YYYY HH:MIS AM') from tbl
    ---pd_finish is already a Date type.

    Thus, anacedent's query will work.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by abhishekdixit

    select pd_finish,to_date(pd_finish,'MM/DD/YYYY HH:MIS AM') from tbl
    ---pd_finish is already a Date type.

    Thus, anacedent's query will work.
    Not true. If pd_finish is a date and you use it in a to_date function call, it will be implicitly converted to a character before it is used in the to_date call. If your NLS date format is not set to 'MM/DD/YYYY HH:MIS AM', the conversion will fail. NEVER, NEVER relay on implicit conversion.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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