×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to show coming week birthdates?
3

How to show coming week birthdates?

How to show coming week birthdates?

(OP)
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

RE: How to show coming week birthdates?

2
Hi

CODE

mydb=# select * from 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)

mydb=# select now();
             now
------------------------------
 2007-02-14 16:51:44.05876+02
(1 row)

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

Feherke.
http://rootshell.be/~feherke/

RE: How to show coming week birthdates?

(OP)
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

RE: How to show coming week birthdates?

Hi

CODE

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;

Feherke.
http://rootshell.be/~feherke/

RE: How to show coming week birthdates?

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?

RE: How to show coming week birthdates?

Hi

CODE

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 order by (to_char(birth,'ddd')::int-to_char(now(),'ddd')::int+div)%div;

-- or

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

Feherke.
http://rootshell.be/~feherke/

RE: How to show coming week birthdates?

Can you PLEASE explain these queries? Because I am new to postgresql.

Thank you

RE: How to show coming week birthdates?

Hi

CODE

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

-- where the difference is greater than or equal to 0 and less than or equal 6
where dif between 0 and 6

-- order by the same difference number
order by dif

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

Feherke.
http://rootshell.be/~feherke/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close