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
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
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!
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
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.
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
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.
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