Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Posts
    5

    Unanswered: Oracle 9 date format in query

    Hello,
    I have strange problem with NLS_DATE_FORMAT on Oracle 9. I have 2 tables:

    Code:
    create table test_table( id varchar2(5), dat date);
    insert into test_table values( '12345', '2009-09-01' );
    create table id_table( id varchar2(5) );
    insert into id_table values( '12345' );
    The date is 1st september 2009. Now, such queries produce different results (using the same date ranges):

    Code:
    alter session set nls_date_format='YYYY-DD-MM';
    select * from test_table where id in ('12345') and dat between '2009-31-08' and '2009-02-09';  -- works
    select * from test_table where id in (select '12345' from dual) and dat between '2009-31-08' and '2009-02-09'; -- doesn't work
    select * from test_table t1 join id_table t2 on t1.id=t2.id and t2.id='12345' and dat between '2009-31-08' and '2009-02-09'; -- works
    select * from test_table where id in (select id from id_table where id='12345') and dat between '2009-31-08' and '2009-02-09'; -- doesn't work
    alter table id_table add constraint pkid primary key (id)
    select * from test_table where id in (select id from id_table where id='12345') and dat between '2009-31-08' and '2009-02-09'; -- works
    Identical queries work just well on Oracle 10. Also, every query works fine if to_date function is used. Is it a problem with Oracle 9? Should I change some database settings?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What does "doesn't work" mean?
    Do you get an error message? If yes, which one?
    Do you get wrong results? If yes, are you sure you have the same data in Oracle9 and Oracle10?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You should always use TO_DATE function.

  4. #4
    Join Date
    Sep 2009
    Posts
    5
    Quote Originally Posted by shammat
    What does "doesn't work" mean?
    Do you get an error message? If yes, which one?
    Do you get wrong results? If yes, are you sure you have the same data in Oracle9 and Oracle10?
    Works - means returns 1 row.
    Doesn't work - means returns no rows - and no errors. Every query should return 1 row. Oracle 9 and 10 have the same data. Same happens with 'DD-MM-YYYY' format and so on. It looks like subqueries work only with default date format (?)

    Quote Originally Posted by Littlefoot
    You should always use TO_DATE function.
    Maybe you're right, but my app has about zillion queries that are not using to_date function and I'm just curious if I can do anything else than rewriting every one of them with to_date.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >default date format (?)
    There is no such thing as "default date format"; when it can be different session to session within DB.

    It appears to me that the undesirable results occur because your "date strings" are in YYYY-DD-MM format which does not collate properly.

    >between '2009-31-08' and '2009-02-09'
    as STRINGS the left string is greater/bigger than the right string; because '31' is greater than '02'.

    If/when Oracle applies TO_CHAR to "dat" & does the BETWEEN, then zero rows should be returned.
    Last edited by anacedent; 09-07-09 at 22:52.
    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.

  6. #6
    Join Date
    Sep 2009
    Posts
    5
    Quote Originally Posted by anacedent
    >default date format (?)
    There is no such thing as "default date format"; when it can be different session to session within DB.

    It appears to me that the undesirable results occur because your "date strings" are in YYYY-DD-MM format which does not collate properly.

    >between '2009-31-08' and '2009-02-09'
    as STRINGS the left string is greater/bigger than the right string; because '31' is greater than '02'.

    If/when Oracle applies TO_CHAR to "dat" & does the BETWEEN, then zero rows should be returned.
    But this select returns 1 row:

    Code:
    select * from test_table where id in ('12345') and dat between '2009-31-08' and '2009-02-09'
    So I guess it's not the case.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    as mentioned, don't rely on the NLS_DATE_FORMAT parameter to load or select dates.
    all your code will break if you run it in a different db with a different parameter value.

    use the to_date function. ESPECIALLY when running sql queries.
    you assume all dates are only midnight and have no timestamp.

    bah, this is one of my pet peeves. Whenever I see code that assumes what
    the db NLS_DATE_FORMAT is I just assume the coder has no idea what a DATE is and what it stores.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Feb 2009
    Posts
    62
    @Amnezjak - I don't get your results on 9.2.0.8

    Code:
    SQL> create table test_table( id varchar2(5), dat date);
    
    Table created.
    
    SQL> insert into test_table values( '12345', to_date('2009-09-01','yyyy-mm-dd') );
    
    1 row created.
    
    SQL> create table id_table( id varchar2(5) );
    
    Table created.
    
    SQL> insert into id_table values( '12345' );
    
    1 row created.
    
    SQL> 
    SQL> alter session set nls_date_format='YYYY-DD-MM';
    
    Session altered.
    
    SQL> 
    SQL> select * from test_table where id in ('12345') 
      2  and dat between '2009-31-08' and '2009-02-09'; 
    
    ID    DAT
    ----- ----------
    12345 2009-01-09
    
    SQL> 
    SQL> select * from test_table where id in (select '12345' from dual) 
      2  and dat between '2009-31-08' and '2009-02-09'; 
    
    ID    DAT
    ----- ----------
    12345 2009-01-09
    
    SQL> 
    SQL> select * from test_table t1 join id_table t2 on t1.id=t2.id and t2.id='12345' 
      2  and dat between '2009-31-08' and '2009-02-09'; 
    
    ID    DAT        ID
    ----- ---------- -----
    12345 2009-01-09 12345
    
    SQL> 
    SQL> select * from test_table where id in (select id from id_table where id='12345') 
      2  and dat between '2009-31-08' and '2009-02-09'; 
    
    ID    DAT
    ----- ----------
    12345 2009-01-09
    
    SQL> 
    SQL> alter table id_table add constraint pkid primary key (id);
    
    Table altered.
    
    SQL> 
    SQL> select * from test_table where id in (select id from id_table where id='12345') 
      2  and dat between '2009-31-08' and '2009-02-09'; 
    
    ID    DAT
    ----- ----------
    12345 2009-01-09
    
    SQL> 
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle9i Release 9.2.0.8.0 - Production
    PL/SQL Release 9.2.0.8.0 - Production
    CORE    9.2.0.8.0       Production
    TNS for Linux: Version 9.2.0.8.0 - Production
    NLSRTL Version 9.2.0.8.0 - Production
    Can you post a cut and paste of an SQL*Plus session (like I just have) showing what happens when you run the code on your system.

    You might want to include a TO_DATE in the initial insert as that will guarantee what the actual date that gets inserted is.

    [Edited to use more readable queries and results]

  9. #9
    Join Date
    Sep 2009
    Posts
    5
    Quote Originally Posted by JRowbottom
    Can you post a cut and paste of an SQL*Plus session (like I just have) showing what happens when you run the code on your system.

    You might want to include a TO_DATE in the initial insert as that will guarantee what the actual date that gets inserted is.
    Code:
    SQL> create table test_table( id varchar2(5), dat date);
    
    Table created.
    
    SQL> insert into test_table values( '12345', to_date('2009-09-01','YYYY-MM-DD') );
    
    1 row created.
    
    SQL> create table id_table( id varchar2(5) );
    
    Table created.
    
    SQL> insert into id_table values( '12345' );
    
    1 row created.
    
    SQL> 
    SQL> alter session set nls_date_format='YYYY-DD-MM';
    
    Session altered.
    
    SQL> select * from test_table where id in ('12345') and dat between '2009-31-08' and '2009-02-09';
    
    ID    DAT
    ----- ----------
    12345 2009-01-09
    
    SQL> select * from test_table where id in (select '12345' from dual) and dat between '2009-31-08' an
    d '2009-02-09';
    
    no rows selected
    
    SQL> select * from test_table t1 join id_table t2 on t1.id=t2.id and t2.id='12345' and dat between '
    2009-31-08' and '2009-02-09';
    
    ID    DAT        ID
    ----- ---------- -----
    12345 2009-01-09 12345
    
    SQL> select * from test_table where id in (select id from id_table where id='12345') and dat between
     '2009-31-08' and '2009-02-09';
    
    no rows selected
    
    SQL> alter table id_table add constraint pkid primary key (id);
    
    Table altered.
    
    SQL> select * from test_table where id in (select id from id_table where id='12345') and dat between
     '2009-31-08' and '2009-02-09';
    
    ID    DAT
    ----- ----------
    12345 2009-01-09
    
    SQL> 
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    PL/SQL Release 9.2.0.1.0 - Production
    CORE    9.2.0.1.0       Production
    TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
    NLSRTL Version 9.2.0.1.0 - Production
    So maybe it's a matter of version...
    I've tried also to alter other settings as NLS_LANGUAGE, NLS_TERRITORY, NLS_DATE_LANGUAGE, but with no success.

Posting Permissions

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