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!

Help with date calculation

Discussion in 'SQL PL/SQL' started by ssjaronx4, Feb 7, 2011.

  1. ssjaronx4

    ssjaronx4 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Guys,

    Need a quick bit of help with a query. I have a table which stores a persons date of birth. I'm trying to write a query which calculates who has a birthday coming up in the next 30 days. I have written the query as below but cant get the where portion working correctly. Also, is there a way to do 1 month rather than 30 days?


    SELECT PARTYPERSON.PersonName, PARTYPERSON.DOB AS "DOB",
    CUST.CFNAME AS "Customer Name", CUST.CLNAME AS "Customer Surname", CUST.cTELNo AS "Telephone No",
    SALESMAN.EMPNAME AS "Previous Salesman" FROM PARTYPERSON
    JOIN CUST
    ON PARTYPERSON.CID = CUST.CID
    JOIN SALESMAN
    ON CUST.EmpNo = SALESMAN.EmpNo
    WHERE TO_DATE(PARTYPERSON.DOB, 'DD-MM') - TO_DATE(sysdate + 30, 'DD-MM')
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I expect it doesn't work because you have a - instead of an = in the condition, and, yes, you can add months to a date:

    Code (SQL):
     
    SELECT PARTYPERSON.PersonName, PARTYPERSON.DOB AS "DOB",
    CUST.CFNAME AS "Customer Name", CUST.CLNAME AS "Customer Surname", CUST.cTELNo AS "Telephone No",
    SALESMAN.EMPNAME AS "Previous Salesman" FROM PARTYPERSON
    JOIN CUST
    ON PARTYPERSON.CID = CUST.CID
    JOIN SALESMAN
    ON CUST.EmpNo = SALESMAN.EmpNo
    WHERE TO_DATE(PARTYPERSON.DOB, 'DD-MM') = TO_DATE(add_months(sysdate, 1), 'DD-MM');
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    TO_DATE(add_months(sysdate, 1), 'DD-MM') will not work.


    What is the data type of DOB ? If it is not Date, yours is poor table design. Date should always be stored in date field. This has to be explained to get the better answer.

    IF DOB is date field, then query should be ,

    Code (SQL):
    SELECT PARTYPERSON.PersonName, PARTYPERSON.DOB AS "DOB",
    CUST.CFNAME AS "Customer Name", CUST.CLNAME AS "Customer Surname", CUST.cTELNo AS "Telephone No",
    SALESMAN.EMPNAME AS "Previous Salesman" FROM PARTYPERSON
    JOIN CUST
    ON PARTYPERSON.CID = CUST.CID
    JOIN SALESMAN
    ON CUST.EmpNo = SALESMAN.EmpNo
    WHERE PARTYPERSON.DOB BETWEEN TRUNC(SYSDATE) AND  TRUNC(ADD_MONTHS(sysdate,1))+1;
     
    IF DOB is VARCHAR2 field, then query should be ,

    Code (SQL):
    SELECT PARTYPERSON.PersonName, PARTYPERSON.DOB AS "DOB",
    CUST.CFNAME AS "Customer Name", CUST.CLNAME AS "Customer Surname", CUST.cTELNo AS "Telephone No",
    SALESMAN.EMPNAME AS "Previous Salesman" FROM PARTYPERSON
    JOIN CUST
    ON PARTYPERSON.CID = CUST.CID
    JOIN SALESMAN
    ON CUST.EmpNo = SALESMAN.EmpNo
    WHERE TO_DATE(PARTYPERSON.DOB,'DD-MM-YYYY') BETWEEN TRUNC(SYSDATE) AND  TRUNC(ADD_MONTHS(sysdate,1))+1;
     
    'DD-MM-YYYY' could be changed to actual format as stored in DOB field.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Correct and I missed that fact in my response.

    Excellent catch.
     
  5. ssjaronx4

    ssjaronx4 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Guys,

    Thanks for getting back to me on this.

    zargon,

    Good catch on the where statement. Looks like i modified it so many times I missed putting an = or between back in before i posted it.

    rajavu,

    Your final query sort of works but not quite. The DOB field is indeed a date type but the query above brings back any people with a DOB between todays date and within the next month. Thats the stage i got stuck at (well using 30 days rather than a month) as i dont know how to get it to ignore the year. From what I understand, Trunc removes the time portion but cant remove the year portion correct? Or am i just using incorrect syntax?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Per your original post:

    "I'm trying to write a query which calculates who has a birthday coming up in the next 30 days. "

    so I don't understand why Rajavu's query isn't working as it "brings back any people with a DOB between todays date and within the next month." Is there a difference? What would that difference be?
     
  7. ssjaronx4

    ssjaronx4 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    What I meant was if I was born on the 15 feb 1986 then the query would return my birthday as being in the next month as my 25th birthday would be tomorrow. The scenario is for a party planning business and therefore they want to be able to find previous customers who have a birthday coming up within the next month so that they can send marketing information to them.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And Rajavu's query does that, does it not? It returns anyone who has a birthday within the next month.
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    @ Zargon,

    It is a mistake from my side. The query will take the DOB and compare with the current date :)

    This can be achieved by changing the query slightly.

    Here is an example .

    Code (SQL):

    SQL> SELECT EMPNO, ENAME, HIREDATE,
      2  ADD_MONTHS( HIREDATE,CEIL(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12)*12) NEXT_BDAY
      3  FROM EMP ;

         EMPNO ENAME      HIREDATE  NEXT_BDAY
    ---------- ---------- --------- ---------
          7369 SMITH      17-DEC-80 17-DEC-11
          7499 ALLEN      20-FEB-81 20-FEB-11
          7521 WARD       22-FEB-81 22-FEB-11
          7566 JONES      02-APR-81 02-APR-11
          7654 MARTIN     28-SEP-81 28-SEP-11
          7698 BLAKE      01-MAY-81 01-MAY-11
          7782 CLARK      09-JUN-81 09-JUN-11
          7788 SCOTT      19-APR-87 19-APR-11
          7839 KING       17-NOV-81 17-NOV-11
          7844 TURNER     08-SEP-81 08-SEP-11
          7876 ADAMS      23-MAY-87 23-MAY-11
          7900 JAMES      03-DEC-81 03-DEC-11
          7902 FORD       03-DEC-81 03-DEC-11
          7934 MILLER     23-JAN-82 23-JAN-12

    14 ROWS selected.

    SQL> SELECT EMPNO, ENAME, HIREDATE,
      2         ADD_MONTHS( HIREDATE,CEIL(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12)*12) NEXT_BDAY
      3  FROM EMP
      4  WHERE  ADD_MONTHS( HIREDATE,CEIL(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)*12) BETWEEN
      5         TRUNC(SYSDATE) AND ADD_MONTHS(TRUNC(SYSDATE),1) ;

         EMPNO ENAME      HIREDATE  NEXT_BDAY
    ---------- ---------- --------- ---------
          7499 ALLEN      20-FEB-81 20-FEB-11
          7521 WARD       22-FEB-81 22-FEB-11

    SQL>

     
     
  10. bsrinu27

    bsrinu27 Active Member

    Messages:
    3
    Likes Received:
    1
    Trophy Points:
    85
    Location:
    hyderabad
    plz check it out


    this is for 1 month

    SELECT PARTYPERSON.PersonName, PARTYPERSON.DOB AS "DOB",
    CUST.CFNAME AS "Customer Name", CUST.CLNAME AS "Customer Surname", CUST.cTELNo AS "Telephone No",
    SALESMAN.EMPNAME AS "Previous Salesman" FROM PARTYPERSON
    JOIN CUST
    ON PARTYPERSON.CID = CUST.CID
    JOIN SALESMAN
    ON CUST.EmpNo = SALESMAN.EmpNo
    WHERE mod(trunc(months_between(sysdate, PARTYPERSON.DOB) ),12)=1


    for 30 days
    SELECT PARTYPERSON.PersonName, PARTYPERSON.DOB AS "DOB",
    CUST.CFNAME AS "Customer Name", CUST.CLNAME AS "Customer Surname", CUST.cTELNo AS "Telephone No",
    SALESMAN.EMPNAME AS "Previous Salesman" FROM PARTYPERSON
    JOIN CUST
    ON PARTYPERSON.CID = CUST.CID
    JOIN SALESMAN
    ON CUST.EmpNo = SALESMAN.EmpNo
    WHERE sysdate-add_months( PARTYPERSON.DOB,(trunc(months_between(sysdate, PARTYPERSON.DOB) )))<=30
     
    Sadik likes this.