Go4Expert

Go4Expert (http://www.go4expert.com/)
-   PostgreSQL (http://www.go4expert.com/forums/postgresql-forum/)
-   -   How to show coming week birthdate? (http://www.go4expert.com/forums/coming-week-birthdate-t3006/)

khwabsheth 14Feb2007 18:31

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

pradeep 15Feb2007 10:27

Re: How to show coming week birthdate?
 
Which database are you using?

pradeep 15Feb2007 10:28

Re: How to show coming week birthdate?
 
http://www.go4expert.com/showthread.php?t=2769 read this, this might help!

khwabsheth 15Feb2007 11:28

Re: How to show coming week birthdate?
 
Hey Pradeep,

I am using PostgreSQL.

Khwab

khwabsheth 16Feb2007 10:35

Re: How to show coming week birthdate?
 
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 16Feb2007 10:53

Re: How to show coming week birthdate?
 
Code: SQL

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 16Feb2007 13:02

Re: How to show coming week birthdate?
 
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 16Feb2007 13:14

Re: How to show coming week birthdate?
 
Well then first sort by month and then by date, I would do it in mysql this way

Code: SQL

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 16Feb2007 14:27

Re: How to show coming week birthdate?
 
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 16Feb2007 14:44

Re: How to show coming week birthdate?
 
Yeah! That's there, but then you query wont work i guess! Any solution in mind?


All times are GMT +5.5. The time now is 23:29.