Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to show coming week birthdates? 3

Status
Not open for further replies.

khwabsheth

Programmer
Feb 14, 2007
2
IN
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
 
Hi
Code:
[blue]mydb=#[/blue] [b]select[/b] * [b]from[/b] employee;
 name  |   birth
-------+------------
 One   | 1950-01-01
 Two   | 1955-01-15
 Three | 1960-02-01
 Four  | 1965-02-10
 Five  | 1970-02-15
 Six   | 1975-02-17
 Seven | 1980-02-20
 Eight | 1985-02-25
 Nine  | 1990-02-28
(9 rows)

[blue]mydb=#[/blue] [b]select[/b] now();
             now
------------------------------
 2007-02-14 16:51:44.05876+02
(1 row)

[blue]mydb=#[/blue] [b]select[/b] * [b]from[/b] employee [b]where[/b] to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'DDD'[/i])::int [b]between[/b] 0 [b]and[/b] 6;
 name  |   birth
-------+------------
 Five  | 1970-02-15
 Six   | 1975-02-17
 Seven | 1980-02-20
(3 rows)

Feherke.
 
Hello Feherke,

First of all thank you very much for helping me with the query, your query works fine but now I want to display all the birthdays in ascending order so What I have done is

select * from employee where to_char(birth,'ddd')::int-to_char(now(),'DDD')::int between 0 and 6 ORDER BY EXTRACT( DOY FROM birth_date);

This works fine but I will get the problem when I access it between 25th dec to 31 dec. Because for first jan "DOY" will be 1 and for 31st dec, it will be 365 and so it will show 1st jan first.

Can you help me with this? I am sure you will be having solutions to this.

Thank you
 
Hi

Code:
[b]select[/b] * [b]from[/b] employee,([b]select[/b] to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i])::int [b]as[/b] div) foo [b]where[/b] (to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'DDD'[/i])::int+div)%div [b]between[/b] 0 [b]and[/b] 6;

Feherke.
 
Hello Fehereke and All,

select * from employee,(select to_char((to_char(now(),'yyyy')||'-12-31')::date,'ddd')::int as div) foo where (to_char(birth,'ddd')::int-to_char(now(),'DDD')::int+div)%div between 0 and 6;

this quere will just show the result of next week birthdays but it will not show in ascending order order. Also Problem will come when 27th december will come because after 27 th december it is suppose to show result in following format

27 december
28 december
29 december
30 december
31 december
1 january
2 january

but ordinary ascendig query will show result in

1 january
2 january
27 december
28 december
29 december
30 december
31 december

Any suggestions?
 
Hi

Code:
[b]select[/b] * [b]from[/b] employee,([b]select[/b] to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i])::int [b]as[/b] div) foo [b]where[/b] (to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'ddd'[/i])::int+div)%div [b]between[/b] 0 [b]and[/b] 6 [b]order by[/b] (to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'ddd'[/i])::int+div)%div;

[gray]-- or[/gray]

[b]select[/b] * [b]from[/b] ([b]select[/b] *,(to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'ddd'[/i])::int+div)%div [b]as[/b] dif [b]from[/b] employee,([b]select[/b] to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i])::int [b]as[/b] div) foo) bar [b]where[/b] dif [b]between[/b] 0 [b]and[/b] 6 [b]order by[/b] dif;

Feherke.
 
Hi

Code:
[b]select[/b]
*
[b]from[/b] (
  [b]select[/b]
  *,
  [gray]-- difference in days between the birth day's and current day's ordinal number[/gray]
  [gray]-- the differences are made continuous, no negative difference[/gray]
  (to_char(birth,[i]'ddd'[/i])::int-to_char(now(),[i]'ddd'[/i])::int+div)%div [b]as[/b] dif
  [b]from[/b] employee
  ,(
    [b]select[/b]
    [gray]-- ordinal number of the year's last day = number of days in the year[/gray]
    to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i])::int [b]as[/b] div[/gray]
  ) foo
) bar

[gray]-- where the difference is greater than or equal to 0 and less than or equal 6[/gray]
[b]where[/b] dif [b]between[/b] 0 [b]and[/b] 6

[gray]-- order by the same difference number[/gray]
[b]order by[/b] dif

[gray]/*
to_char(now(),[i]'ddd'[/i]) [i]= ordinal number of current day ( ie, 2007-02-19 is the year's 50[sup]th[/sup] day )[/i]
to_char(now(),[i]'ddd'[/i])::int [i]= the character result of the function casted to numeric value for arithmetic operations[/i]
to_char(now(),[i]'yyyy'[/i]) [i]= the year ( ie, 2007-02-19 is in year 2007 )[/i]
to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date [i]= current year's last day ( ie, 2007's last day is 2007-12-31 )[/i]
to_char((to_char(now(),[i]'yyyy'[/i])||[i]'-12-31'[/i])::date,[i]'ddd'[/i]) [i]= ordinal number of the last day of the year( ie, 2007's last day is the year's 365[sup]th[/sup] day )[/i]
*/[/gray]

Feherke.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top