1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Date comparison issue

Discussion in 'SQL PL/SQL' started by Praveen Havargi, Sep 16, 2016.

  1. Praveen Havargi

    Praveen Havargi Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    Hey,

    I have a varchar2 field in one of my Oracle tables - RECORDDT.

    Sample data in this field is
    20160607
    20160608
    20160609

    I want all the data which is recorded in the last 2 Years.

    The way which I'm doing it right now is something like:

    SELECT * FROM RECORDS
    WHERE
    TO_DATE(RECORDDT,'YYYY/MM/DD') > (SELECT ADD_MONTHS( TO_DATE(SYSDATE,'YYYY/MM/DD'), -24 )-1 FROM DUAL)

    I see dates before 2 years in the result set for the above query which is incorrect, but when I use

    SELECT * FROM RECORDS
    WHERE
    TO_DATE(RECORDDT,'YY/MM/DD') > (SELECT ADD_MONTHS( TO_DATE(SYSDATE,'YY/MM/DD'), -24 )-1 FROM DUAL)

    I see data as expected (records which are not older than 2 years). Why is this comparison mismatch happening? Is the issue related to NLS? I tried to convert the dates to number, but again I saw unexpected result.

    Thanks in advance!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Using TO_DATE on SYSDATE is essentially useless, as SYSDATE is already in Oracle's date format. Why you're choosing to store DATE values as strings is a mystery as problems like this can easily arise. Setting up an example and running your two queries and several modifications of them against the varchar2 data I get:

    Code (SQL):

    SQL> CREATE TABLE records(
      2  myid    NUMBER,
      3  mydata  varchar2(40),
      4  recorddt         varchar2(10));

    TABLE created.

    SQL>
    SQL> BEGIN
      2           FOR i IN 1..1000 loop
      3               INSERT INTO records
      4               VALUES(i, 'Record '||i, to_char(sysdate - (4*i), 'YYYY/MM/DD'));
      5           END loop;
      6
      7           commit;
      8  END;
      9  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> SELECT * FROM RECORDS
      2  WHERE
      3  TO_DATE(RECORDDT,'YYYY/MM/DD') > (SELECT ADD_MONTHS( TO_DATE(SYSDATE,'YYYY/MM/DD'), -24 )-1 FROM DUAL);

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           242 Record 242                               2014/01/22                
           243 Record 243                               2014/01/18                
           244 Record 244                               2014/01/14                
           245 Record 245                               2014/01/10                
           246 Record 246                               2014/01/06                
           247 Record 247                               2014/01/02                
           248 Record 248                               2013/12/29                
           249 Record 249                               2013/12/25                
           250 Record 250                               2013/12/21                
           251 Record 251                               2013/12/17                
           252 Record 252                               2013/12/13                
    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           583 Record 583                               2010/04/29                
           584 Record 584                               2010/04/25                
           585 Record 585                               2010/04/21                
           586 Record 586                               2010/04/17                
           587 Record 587                               2010/04/13                
           588 Record 588                               2010/04/09                
           589 Record 589                               2010/04/05                
           590 Record 590                               2010/04/01                
           591 Record 591                               2010/03/28                
           592 Record 592                               2010/03/24                
           593 Record 593                               2010/03/20                

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           594 Record 594                               2010/03/16                
           595 Record 595                               2010/03/12                
           596 Record 596                               2010/03/08                
           597 Record 597                               2010/03/04                
           598 Record 598                               2010/02/28                
           599 Record 599                               2010/02/24                
           600 Record 600                               2010/02/20                
           601 Record 601                               2010/02/16                
           602 Record 602                               2010/02/12                
           603 Record 603                               2010/02/08                
           604 Record 604                               2010/02/04                

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           971 Record 971                               2006/01/28                
           972 Record 972                               2006/01/24                
           973 Record 973                               2006/01/20                
           974 Record 974                               2006/01/16                
           975 Record 975                               2006/01/12                
           976 Record 976                               2006/01/08                
           977 Record 977                               2006/01/04                
           978 Record 978                               2005/12/31                
           979 Record 979                               2005/12/27                
           980 Record 980                               2005/12/23                
           981 Record 981                               2005/12/19                

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           982 Record 982                               2005/12/15                
           983 Record 983                               2005/12/11                
           984 Record 984                               2005/12/07                
           985 Record 985                               2005/12/03                
           986 Record 986                               2005/11/29                
           987 Record 987                               2005/11/25                
           988 Record 988                               2005/11/21                
           989 Record 989                               2005/11/17                
           990 Record 990                               2005/11/13                
           991 Record 991                               2005/11/09                
           992 Record 992                               2005/11/05                

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           993 Record 993                               2005/11/01                
           994 Record 994                               2005/10/28                
           995 Record 995                               2005/10/24                
           996 Record 996                               2005/10/20                
           997 Record 997                               2005/10/16                
           998 Record 998                               2005/10/12                
           999 Record 999                               2005/10/08                
          1000 Record 1000                              2005/10/04                
           710 Record 710                               2008/12/07                
           711 Record 711                               2008/12/03                
           712 Record 712                               2008/11/29                

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
            12 Record 12                                2016/07/30                
            13 Record 13                                2016/07/26                
            14 Record 14                                2016/07/22                
            15 Record 15                                2016/07/18                
            16 Record 16                                2016/07/14                
            17 Record 17                                2016/07/10                
            18 Record 18                                2016/07/06                
            19 Record 19                                2016/07/02                
            20 Record 20                                2016/06/28                
            21 Record 21                                2016/06/24                
            22 Record 22                                2016/06/20                

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           232 Record 232                               2014/03/03                
           233 Record 233                               2014/02/27                
           234 Record 234                               2014/02/23                
           235 Record 235                               2014/02/19                
           236 Record 236                               2014/02/15                
           237 Record 237                               2014/02/11                
           238 Record 238                               2014/02/07                
           239 Record 239                               2014/02/03                
           240 Record 240                               2014/01/30                
           241 Record 241                               2014/01/26                

    1000 ROWS selected.

    SQL>
    SQL> SELECT * FROM RECORDS
      2  WHERE
      3  TO_DATE(RECORDDT,'YY/MM/DD') > (SELECT ADD_MONTHS( TO_DATE(SYSDATE,'YY/MM/DD'), -24 )-1 FROM DUAL);

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
             1 Record 1                                 2016/09/12                
             2 Record 2                                 2016/09/08                
             3 Record 3                                 2016/09/04                
             4 Record 4                                 2016/08/31                
             5 Record 5                                 2016/08/27                
             6 Record 6                                 2016/08/23                
             7 Record 7                                 2016/08/19                
             8 Record 8                                 2016/08/15                
             9 Record 9                                 2016/08/11                
            10 Record 10                                2016/08/07                
            11 Record 11                                2016/08/03                

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           177 Record 177                               2014/10/09                
           178 Record 178                               2014/10/05                
           179 Record 179                               2014/10/01                
           180 Record 180                               2014/09/27                
           181 Record 181                               2014/09/23                
           182 Record 182                               2014/09/19                

    182 ROWS selected.

    SQL>
    SQL> SELECT * FROM RECORDS
      2  WHERE
      3  TO_DATE(RECORDDT,'RRRR/MM/DD') > (SELECT ADD_MONTHS( TO_DATE(SYSDATE,'RRRR/MM/DD'), -24 )-1 FROM DUAL);

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
             1 Record 1                                 2016/09/12                
             2 Record 2                                 2016/09/08                
             3 Record 3                                 2016/09/04                
             4 Record 4                                 2016/08/31                
             5 Record 5                                 2016/08/27                
             6 Record 6                                 2016/08/23                
             7 Record 7                                 2016/08/19                
             8 Record 8                                 2016/08/15                
             9 Record 9                                 2016/08/11                
            10 Record 10                                2016/08/07                
            11 Record 11                                2016/08/03                

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           177 Record 177                               2014/10/09                
           178 Record 178                               2014/10/05                
           179 Record 179                               2014/10/01                
           180 Record 180                               2014/09/27                
           181 Record 181                               2014/09/23                
           182 Record 182                               2014/09/19                

    182 ROWS selected.

    SQL>
    SQL> SELECT * FROM records
      2  WHERE
      3  to_date(recorddt,'YYYY/MM/DD') > (SELECT add_months(sysdate, -24 )-1 FROM dual)
      4  ORDER BY recorddt;

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           182 Record 182                               2014/09/19                
           181 Record 181                               2014/09/23                
           180 Record 180                               2014/09/27                
           179 Record 179                               2014/10/01                
           178 Record 178                               2014/10/05                
           177 Record 177                               2014/10/09                
           176 Record 176                               2014/10/13                
           175 Record 175                               2014/10/17                
           174 Record 174                               2014/10/21                
           173 Record 173                               2014/10/25                
           172 Record 172                               2014/10/29                

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
             6 Record 6                                 2016/08/23                
             5 Record 5                                 2016/08/27                
             4 Record 4                                 2016/08/31                
             3 Record 3                                 2016/09/04                
             2 Record 2                                 2016/09/08                
             1 Record 1                                 2016/09/12                

    182 ROWS selected.

    SQL>
    SQL> SELECT * FROM records
      2  WHERE
      3  to_date(recorddt,'YY/MM/DD') > (SELECT add_months(sysdate, -24 )-1 FROM dual)
      4  ORDER BY recorddt;

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           182 Record 182                               2014/09/19                
           181 Record 181                               2014/09/23                
           180 Record 180                               2014/09/27                
           179 Record 179                               2014/10/01                
           178 Record 178                               2014/10/05                
           177 Record 177                               2014/10/09                
           176 Record 176                               2014/10/13                
           175 Record 175                               2014/10/17                
           174 Record 174                               2014/10/21                
           173 Record 173                               2014/10/25                
           172 Record 172                               2014/10/29                

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
             6 Record 6                                 2016/08/23                
             5 Record 5                                 2016/08/27                
             4 Record 4                                 2016/08/31                
             3 Record 3                                 2016/09/04                
             2 Record 2                                 2016/09/08                
             1 Record 1                                 2016/09/12                

    182 ROWS selected.

    SQL>
    SQL> SELECT * FROM records
      2  WHERE
      3  to_date(recorddt,'RRRR/MM/DD') > (SELECT add_months(sysdate, -24 )-1 FROM dual)
      4  ORDER BY recorddt;

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           182 Record 182                               2014/09/19                
           181 Record 181                               2014/09/23                
           180 Record 180                               2014/09/27                
           179 Record 179                               2014/10/01                
           178 Record 178                               2014/10/05                
           177 Record 177                               2014/10/09                
           176 Record 176                               2014/10/13                
           175 Record 175                               2014/10/17                
           174 Record 174                               2014/10/21                
           173 Record 173                               2014/10/25                
           172 Record 172                               2014/10/29                

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
             6 Record 6                                 2016/08/23                
             5 Record 5                                 2016/08/27                
             4 Record 4                                 2016/08/31                
             3 Record 3                                 2016/09/04                
             2 Record 2                                 2016/09/08                
             1 Record 1                                 2016/09/12                

    182 ROWS selected.

    SQL>
    SQL> SELECT * FROM records
      2  WHERE
      3  to_date(recorddt,'RR/MM/DD') > (SELECT add_months(sysdate, -24 )-1 FROM dual)
      4  ORDER BY recorddt;

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
           182 Record 182                               2014/09/19                
           181 Record 181                               2014/09/23                
           180 Record 180                               2014/09/27                
           179 Record 179                               2014/10/01                
           178 Record 178                               2014/10/05                
           177 Record 177                               2014/10/09                
           176 Record 176                               2014/10/13                
           175 Record 175                               2014/10/17                
           174 Record 174                               2014/10/21                
           173 Record 173                               2014/10/25                
           172 Record 172                               2014/10/29                

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ----------                
             6 Record 6                                 2016/08/23                
             5 Record 5                                 2016/08/27                
             4 Record 4                                 2016/08/31                
             3 Record 3                                 2016/09/04                
             2 Record 2                                 2016/09/08                
             1 Record 1                                 2016/09/12                

    182 ROWS selected.

    SQL>
     
    Please note that only your query using the 'YYYY/MM/DD' format returns more records than it 'should'; this is because the YYYY year specifier doesn't account for the century, where the modified query using the RRRR year specifier does, and you're using that specifier to uselessly convert a DATE to a DATE. That was the ONLY change to your original query that caused it to return the proper number of records. Notice also that the remaining queries, even with the YYYY specifier, returned the correct number of records because they are not trying to convert a DATE into a DATE.

    Changing the RECORDDT column to be an actual DATE field simplifies the code and, again, returns the correct number of records:

    Code (SQL):
    SQL> CREATE TABLE records(
      2  myid    NUMBER,
      3  mydata  varchar2(40),
      4  recorddt         DATE);

    TABLE created.

    SQL>
    SQL> BEGIN
      2           FOR i IN 1..1000 loop
      3               INSERT INTO records
      4               VALUES(i, 'Record '||i, sysdate - (4*i));
      5           END loop;
      6
      7           commit;
      8  END;
      9  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> SELECT * FROM records
      2  WHERE
      3  recorddt > (SELECT add_months(sysdate, -24 )-1 FROM dual)
      4  ORDER BY recorddt;

          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ---------                  
           182 Record 182                               19-SEP-14                  
           181 Record 181                               23-SEP-14                  
           180 Record 180                               27-SEP-14                  
           179 Record 179                               01-OCT-14                  
           178 Record 178                               05-OCT-14                  
           177 Record 177                               09-OCT-14                  
           176 Record 176                               13-OCT-14                  
           175 Record 175                               17-OCT-14                  
           174 Record 174                               21-OCT-14                  
           173 Record 173                               25-OCT-14                  
           172 Record 172                               29-OCT-14                  

    ...
          MYID MYDATA                                   RECORDDT                  
    ---------- ---------------------------------------- ---------                  
             6 Record 6                                 23-AUG-16                  
             5 Record 5                                 27-AUG-16                  
             4 Record 4                                 31-AUG-16                  
             3 Record 3                                 04-SEP-16                  
             2 Record 2                                 08-SEP-16                  
             1 Record 1                                 12-SEP-16                  

    182 ROWS selected.

    SQL>
     
    You need to read here:

    https://dfitzjarrell.wordpress.com/2016/02/17/that-aint-right/

    to see WHY you shouldn't be storing DATE values as strings.
     
    Praveen Havargi likes this.
  3. Praveen Havargi

    Praveen Havargi Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    Thanks a lot! This helped. To answer your question on why I'm storing date as string, well it's an already existing table from source system wherein it even has data at times which are not exactly dates.. Say 00000000 or xxxxxxxx.

    Thanks again!
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That is why 'ridiculous' dates should be stored for non-existent values, such as 01-01-0000 or 12-31-9999; since they can't possibly be an actual date for the data they can be ignored and you can still use a DATE field rather than stuffing date values into a VARCHAR2.