[khwabsheth]

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

[pradeep]

Which database are you using?

[pradeep]

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

[khwabsheth]

Hey Pradeep,

I am using PostgreSQL.

Khwab

[khwabsheth]

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

[pradeep]

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!

[khwabsheth]

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

[pradeep]

Well then first sort by month and then by date, I would do it in mysql this way

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.

[khwabsheth]

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

[pradeep]

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