PDA

View Full Version : SQL question!!


nayasavera
July 7th, 2003, 07:13 AM
I have a Partner table with a date of birth field. I want to find all partners whose birthdays fall in the time period (today +- 7 days).


Partner
---------

ID DOB
-- ----------

1 10.10.1971
2 30.11.1977
3 04.07.1965
5 08.07.1978
6 02.07.1955
7 11.07.1948


So, if the query is run today (07.07), I should be able to get the last 4 rows.

Any SQL experts here ?

I am using DB2 ... if it matters.

Thanks in advance.
:)

GpeL
July 7th, 2003, 07:24 AM
I am a sybase guy but let me take a stab at it.

I believe DB2 has a current date function.

So your query would be

select * from table_name
where dob >= current date - 4 days
or dob <= current date + 4 days


In sybase I would use a date diff.

Also if DOB is stored in a char field

then your where clause will change to

where todate(DOB) >= blah blah.

nayasavera
July 7th, 2003, 07:29 AM
Originally posted by GpeL
I am a sybase guy but let me take a stab at it.

I believe DB2 has a current date function.

So your query would be

select * from table_name
where dob >= current date - 4 days
or dob <= current date + 4 days


In sybase I would use a date diff.

Also if DOB is stored in a char field

then your where clause will change to

where todate(DOB) >= blah blah.

thanks yaar lekin problem solve nahin hui.

DB2 has current date function.

But what you wrote would also take year (of DOB) into account. I don't want year to be considered.

The records

3 04.07.1965
5 08.07.1978
6 02.07.1955
7 11.07.1948

won't be returned with this query but I want them.

Thoda aur try maar de bhai mere 2-3 ghante bach jaayenge.

Zen
July 7th, 2003, 07:45 AM
use month and dayofmonth functions or their equivelent in db2.
some thing like -


PS : There is a flaw in this - it will find it for the same month so you won't get them for those falling on the next or prev month. There are some other functions like day of year or something similar - say partner is the name of the table -

select * from partner where month(partner.dob) = month(current_date) and
(abs(dayofmonth(partner.dob) - dayofmonth(current_date)) <= 3)

or something on these lines! jaldee me likha hai check kar lena - I generally use 4gl to do this stuff not SQL. so this may not be the best solution!

nayasavera
July 7th, 2003, 08:01 AM
Originally posted by Zen

select * from partner where month(partner.dob) = month(current_date) and
(abs(dayofmonth(partner.dob) - dayofmonth(current_date)) <= 3)


hmmm.. lekin 25th se 5th tak ki dates me problem aayegi. :(

Thanks anyway.

Zen
July 7th, 2003, 08:04 AM
Originally posted by nayasavera
hmmm.. lekin 25th se 5th tak ki dates me problem aayegi. :(

Thanks anyway.

simple yaar use BETWEEN and INTERVAL combination for that ... read the help. ...you won't need ABS function in that case.

Zen
July 7th, 2003, 08:10 AM
or .. use DAYOFYEAR ... ;)

select * from table where
ABS(DAYOFYEAR(table.datefield) - DAYOFYEAR(current date)) <=7.

nayasavera
July 7th, 2003, 08:20 AM
Originally posted by Zen
or .. use DAYOFYEAR ... ;)

select * from table where
ABS(DAYOFYEAR(table.datefield) - DAYOFYEAR(current date)) <=7.

:D

Oye itni der se kyun ghuma riya tha ? :D

DAYOFYEAR kyaa mast cheez bataayi hai. :up:

Kaam ho gaya paahji. :up:

Dhanyavaad, shukriya, thanks, merci, danke schön !!!!!!

Zen
July 7th, 2003, 08:30 AM
Ye style maine recently Diplu se seekhi hai - ek baar me help mat karo - pahale saamne wale ka dimaag dahee kar do!!

wo kahaawat hai naa - bakaree doodh detee hai saath-saath me hagtee bhee hai!! :D

j/K - (I was googling sure this function was available in DB2)

Big-G
July 7th, 2003, 08:31 AM
Nayasavera = Loverboy !!! :eek:

nayasavera
July 7th, 2003, 08:33 AM
Originally posted by Big-G
Nayasavera = Loverboy !!! :eek:

:D

To kyaa tere ko ab pata chala ?

erm...i thot..errmm u knew it. u dint?

Zen
July 7th, 2003, 08:36 AM
Originally posted by Zen
use month and dayofmonth functions or their equivelent in db2.
some thing like -


PS : There is a flaw in this - it will find it for the same month so you won't get them for those falling on the next or prev month. There are some other functions like day of year or something similar - say partner is the name of the table -

select * from partner where month(partner.dob) = month(current_date) and
(abs(dayofmonth(partner.dob) - dayofmonth(current_date)) <= 3)

or something on these lines! jaldee me likha hai check kar lena - I generally use 4gl to do this stuff not SQL. so this may not be the best solution!

saala - hint bhee ki thee!! NewMorn tu bhee yaar!!!

nayasavera
July 7th, 2003, 08:41 AM
Originally posted by Zen
saala - hint bhee ki thee!! NewMorn tu bhee yaar!!!

:D
Yaar jab paka pakaaya maal milne ki ummeed ho to mehnat kyun ki jaaye. Mere 2 ghante bach gaye. Ab Baitbaazi me partcipate karte hain.