Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    8

    Unanswered: Default dateformat in Oracle ??

    Friends,

    My requirement is to find the difference in minutes between two date variables. say in the following case,

    .......
    date1 date;
    date2 date;
    ......
    date1 := sysdate;
    date2 := sysdate + 10/1440;
    .....
    if (date1 > date2 )
    ....
    This is not working as the default date format returns only the date part .So i have changed the date format by alter session set nls_date_format = 'DD-MON-YYYY HH:MIS'.

    .......
    date1 date;
    date2 date;
    ......
    execute immediate 'alter session set nls_date_format = 'DD-MON-YYYY HH:MIS';
    date1 := sysdate;
    date2 := sysdate + 10/1440;
    .....
    if (date1 > date2 )
    ....
    Whether i need to restore the default date format within the procedure by another alter session cmd and if so what is the default date format of the Oracle datebase ??

    **SqlPgmr**

  2. #2
    Join Date
    Nov 2005
    Posts
    17
    All dates are manipulated internally in the same format so it doesn't matter what your NLS_DATE_FORMAT parameter is set to. The parameter is for retrieval (i.e. display) purposes only.

    To manipulate the time you can simply use TO_CHAR(date, 'DD-MON-YYYY HH24:MI') [as an example] rather than ALTER SESSION.

    Example:

    Code:
    SQL> SELECT TO_CHAR(SYSDATE, 'HH24:MI')
      2  FROM dual;
    
    TO_CH
    -----
    00:31

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Have a look at this thread, that should help you.

    HTH & Regards,

    RBARAER
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I don't know whether it's significant, but in your example the IF condition is not met because date2 is after date1.
    Last edited by WilliamR; 11-22-05 at 20:29.

Posting Permissions

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