1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

How to show coming week birthdate?

Discussion in 'PostgreSQL' started by khwabsheth, Feb 14, 2007.

  1. khwabsheth

    khwabsheth Banned

    Joined:
    Feb 14, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Hello All,

    I want to display the employees list whose birth day is coming in next week, in database I am storing birth date in MM/DD/YYYY format.

    Anyone can help me with this?

    Thank you

    Khwab
     
  2. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Which database are you using?
     
  3. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
  4. khwabsheth

    khwabsheth Banned

    Joined:
    Feb 14, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Hey Pradeep,

    I am using PostgreSQL.

    Khwab
     
  5. khwabsheth

    khwabsheth Banned

    Joined:
    Feb 14, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Well,

    I have find out the query which shows the coming week birthdays but the only problem is it is not showing in the order.

    SELECT * FROM employees WHERE to_char(birth_date,'ddd')::int-to_char(now(),'DDD')::int between 0 and 8 ;

    if in db records are
    name | birth
    -------+------------
    One | 02/18/1981
    Two | 02/16/1985
    Three | 03/27/1976
    Four | 02/21/1970
    Five | 02/15/1986
    Six | 07/15/1981

    then the output should be as follows

    02/15/1986
    02/16/1985
    02/18/1981
    02/21/1970
     
  6. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Code:
    SELECT * FROM employees WHERE to_char(birth_date,'ddd')::int-to_char(now(),'DDD')::int between 0 and 8 ORDER BY birth_date;
    One more method is to find the current week of the year, and search for all birthdays in the same week!
     
  7. khwabsheth

    khwabsheth Banned

    Joined:
    Feb 14, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Hello Pradeep,

    Using order by birth_date will not solve my problem,

    consider the following table

    One | 02/18/1981
    Two | 02/17/1985
    Three | 03/27/1976
    Four | 02/21/1970
    Five | 02/16/1986
    Six | 07/15/1981


    I want output as

    02/15/1986 i.e. Feb 16
    02/16/1985 i.e. Feb 17
    02/18/1981 i.e. Feb 18
    02/21/1970 i.e. Feb 22

    But using order by birth_date will give me out put as

    02/21/1970 i.e. Feb 22
    02/18/1981 i.e. Feb 18
    02/16/1985 i.e. Feb 17
    02/15/1986 i.e. Feb 16

    Which i dont want in this order, as days and month are not in order.

    Any other option?

    Thanks
    Khwab
     
  8. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Well then first sort by month and then by date, I would do it in mysql this way

    Code:
     SELECT * FROM emp_profile ORDER BY MONTH(dob),DAY(dob);
     /* MONTH is a function which extracts the month from dob
         DAY is a function which extracts the date from dob
     */
     
    Hope this solves your problem.
     
  9. khwabsheth

    khwabsheth Banned

    Joined:
    Feb 14, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Hey Pradeep,

    Your query is good and it will work nice except in one condition,

    Think that today is 27 december and I am showing birthdays in next week and i have few records like 29 december , 1 january, 28 december and 2 january in database.

    So in that case the result should be

    28 december
    29 december
    1 january
    2 january

    But your query will show the result
    1 january
    2 january
    28 december
    29 december

    Thanks ,
    Khwab Sheth
     
  10. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Yeah! That's there, but then you query wont work i guess! Any solution in mind?
     
  11. khwabsheth

    khwabsheth Banned

    Joined:
    Feb 14, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    I tried order by EXTRACT( DOY FROM birth_date ) which does the similar work which you said , but it will also give the problem for last week of the year.

    I guess I have to use CASE statement and check if DOY (which gives day of year
    for eg. 17 feb = 31+17 = 48 days) is greater then 259 ( that is 365 - 1 week) then one order by else another order by . But again i will get problem for leap year.

    Give me if you have better suggestions.
     
  12. khwabsheth

    khwabsheth Banned

    Joined:
    Feb 14, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    It should be 359 not 259 in above post
     
  13. khwabsheth

    khwabsheth Banned

    Joined:
    Feb 14, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Hey,

    Finally I am done with my problem , this works fine in postgresql. But it will not work in mysql as mysql doesnt support "::" .

    $strSql = "SELECT * FROM admin.employees,(SELECT to_char((to_char(now(),'yyyy')||'-12-31')::date,'ddd')::int as div) foo WHERE (to_char(birth_date,'ddd')::int-to_char(now(),'ddd')::int+div)%div BETWEEN 0 and 7 ORDER BY (to_char(birth_date,'ddd')::int-to_char(now(),'ddd')::int+div)%div;";

    Enjoy!

    Khwab
     
  14. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Cool, i will try it in MySQL
     

Share This Page