Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    37

    Unanswered: subtracting sysdate from today's date!

    Hello. I have the following table-
    Code:
    drop table test;
    create table test(
    	dob date
    );
    and following data-
    Code:
    DOB
    ---------
    22-JUN-10
    22-JUL-10
    22-MAY-10
    Today's date is 22-JUL-10.

    I have the following result-
    Code:
    SQL> select * from test where dob-sysdate=0;
    
    no rows selected
    
    SQL> select * from test where sysdate-dob=0;
    
    no rows selected
    but there should be at least one entry (22-JUL-10).

    What is wrong here? Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SYSDATE contains time (hours, minutes and seconds). Regarding your data, you should TRIM(SYSDATE), which would remove time portion.

  3. #3
    Join Date
    Mar 2007
    Posts
    37
    Thanks littlefoot. That clarifies the issue. But why SELECT sysdate from DUAL omits hours, minutes, and seconds?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Because your NLS settings are set not to display those values.
    Code:
    SQL> alter session set nls_date_format = 'dd.mm.yyyy';
    
    Session altered.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    ----------
    22.07.2010
    
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    -------------------
    22.07.2010 08:23:55
    
    SQL>

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Forgot to mention - you could also use the TO_CHAR function to display what you are interested in:
    Code:
    SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') from dual;
    
    TO_CHAR(SYSDATE,'DD
    -------------------
    22.07.2010 08:24:51
    
    SQL>

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Littlefoot View Post
    SYSDATE contains time (hours, minutes and seconds). Regarding your data, you should TRIM(SYSDATE), which would remove time portion.
    I think you mean TRUNC(sysdate), not trim()

    TRIM() is for character datatypes.

    TRIM(sysdate) will first convert sysdate to a character value by using implicit conversion rules and then trim of any trailing spaces from that result and will return a character value as weill. So DOB - TRIM(SYSDATE) will not work.

    To rule out any time portion in DOB, the best thing would be to use trunc(dob) - trunc(sysdate).

    BUT: that won't work either if DOB is a birthday (as the column name suggests) with a value of e.g. 22-07-1950.
    trunc(dob) - trunc(sysdate) will then not return zero (because of the year).

    To find out if someone has birthday today a comparison like
    Code:
    WHERE extract(month from dob) = extract(month from sysdate) 
      AND extract(day from dob) = extract(day from sysdate)

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Ooops! LOL, right. TRUNC is what I should have said. Why didn't I, eh? Sorry.

  8. #8
    Join Date
    Jul 2010
    Posts
    3

    venkat

    Select dob,
    trunc(months_between(sysdate,dob)/12) years,
    trunc(mod(months_between(sysdate,dob),12)) months
    from test;

Posting Permissions

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