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!

Problem in date comparison

Discussion in 'SQL PL/SQL' started by JLone, Sep 24, 2009.

  1. JLone

    JLone Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    I am having problem in date comparison using to_date and to_char functions.

    When i use to_date function in below mentioned queries it returns me wrong result

    Query No:1
    =========
    Select to_date(sysdate, 'dd/mm/yyyy') from dual
    where to_date(sysdate, 'dd/mm/yyyy')=to_date('24/09/2009','dd/mm/yyyy')

    what actualy happens is that to_date(sysdate, 'dd/mm/yyyy') returns '24/09/0009' where as system date is '24/09/2009'. This query will work fine if i use 'dd/mm/yy' instead of 'dd/mm/yyyy'.

    Then i have to compare some other dates and tried query no 2.

    Query No:2
    =========
    Select to_date(Date_of_Birth, 'dd/mm/yyyy') from Person_Profile
    where to_date(Date_of_Birth, 'dd/mm/yy') < to_date('24/09/2005','dd/mm/yy')

    It works fine with records of current century but does not return me results of previous centuries, as it compares only last 2 digits.

    I have also tried this query with to_char(Date_of_Birth, 'dd/mm/yy') < to_char('24/09/2005','dd/mm/yy') then it returns an Error "Not a valid number".

    I would be very thankful if any one can help me solve this problem.

    Best Regards,
    Jawwad Lone
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your queries don't work because you cannot use to_date() on a date value. Presuming you meant to_char I've run the modified queries and have generated this output:

    Code (SQL):
    SQL>
    SQL> --
    SQL> -- 'Bad' queries which don't work
    SQL> -- but not for the reported reason
    SQL> --
    SQL> SELECT to_date(sysdate, 'dd/mm/yyyy') FROM dual
      2  WHERE to_date(sysdate, 'dd/mm/yyyy')=to_date('24/09/2009','dd/mm/yyyy');
    WHERE to_date(sysdate, 'dd/mm/yyyy')=to_date('24/09/2009','dd/mm/yyyy')
                  *
    ERROR at line 2:
    ORA-01858: a non-NUMERIC CHARACTER was found WHERE a NUMERIC was expected


    SQL>
    SQL> SELECT to_date(Date_of_Birth, 'dd/mm/yyyy') FROM Person_Profile
      2  WHERE to_date(Date_of_Birth, 'dd/mm/yy') < to_date('24/09/2005','dd/mm/yy');
    WHERE to_date(Date_of_Birth, 'dd/mm/yy') < to_date('24/09/2005','dd/mm/yy')
                  *
    ERROR at line 2:
    ORA-01858: a non-NUMERIC CHARACTER was found WHERE a NUMERIC was expected


    SQL>
    SQL> --
    SQL> -- 'Bad' queries
    SQL> --
    SQL> SELECT to_char(sysdate, 'dd/mm/yyyy') FROM dual
      2  WHERE sysdate=to_date('24/09/2009','dd/mm/yyyy');

    no ROWS selected

    SQL>
    SQL> SELECT to_char(sysdate, 'dd/mm/yyyy') FROM dual
      2  WHERE trunc(sysdate)=to_date('24/09/2009','dd/mm/yyyy');

    TO_CHAR(SY
    ----------
    24/09/2009

    SQL>
    SQL> SELECT to_char(Date_of_Birth, 'dd/mm/yyyy') FROM Person_Profile
      2  WHERE Date_of_Birth < to_date('24/09/2005','dd/mm/yy');

    TO_CHAR(DA
    ----------
    23/09/2005
    22/09/2005
    21/09/2005
    20/09/2005
    19/09/2005
    18/09/2005
    17/09/2005
    16/09/2005
    15/09/2005
    14/09/2005
    23/09/2005
    ...  
    I don't return the same results you claim; which release of Oracle are you using?

    To address your second issue, of not being able to return 1985 when the two-digit year is 85 you'll need to change your format specifier from yy to rr:

    Code (SQL):
    SQL> SELECT to_char(Date_of_Birth, 'dd/mm/yyyy') FROM Person_Profile
      2  WHERE Date_of_Birth < to_date('24/09/85','dd/mm/rr');

    TO_CHAR(DA
    ----------
    23/09/1985
    22/09/1985
    21/09/1985
    20/09/1985
    19/09/1985
    18/09/1985
    17/09/1985
    16/09/1985
    15/09/1985
    14/09/1985
    13/09/1985
    ...
    Using yy would have returned all records from the table as it would have generated a year of 2085 as the limit; notice using rr generated the 'proper' year of 1985.
     
    JLone likes this.
  3. JLone

    JLone Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thank you. The problem got resolved by using rr as year format in todate() function.