How to show coming week birthdate?

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

 

Which database are you using?

 

http://www.go4expert.com/showthread.php?t=2769 read this, this might help!

 

Hey Pradeep,

I am using PostgreSQL.

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

 

Yeah! That's there, but then you query wont work i guess! Any solution in mind?

 

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.

 

It should be 359 not 259 in above post

 

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

 

Cool, i will try it in MySQL