Finding birthday of person n next 7 days

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding birthday of person n next 7 days

Author

Topic

crazyme
Starting Member

11 Posts

Posted - 2006-08-24 : 07:51:41

I have a table named programmer, where my requirement is to find people who are celebrating their birthday within that week. The query which i'm using is given below...but getting an error, don't know where i misfoot...(the table contains two columns, name,dob)

select name,dob,from programmer
where
datediff(d,getdate(),convert(datetime,convert(varchar,year(getdate()) + '-' + convert(varchar,month(dob)) + '-' +
convert(varchar,day(dob))) between 0 and 7

Knowledge grows when shared...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 07:57:10

[code]
select name,dob,from programmer
where
dob between getdate() and dateadd(dd,7,getdate())

and if you wanna get all the records in that particular week then, check out this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

and make use of the function.
[/code]

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:00:41

A quick one could be


SELECT	*
FROM	Programmer
WHERE	32 * MONTH(dob) + DAY(dob) BETWEEN 32 * MONTH(DATEADD(day, 6, getdate())) + DAY(DATEADD(day, 6, getdate()))

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:01:31

quote:
Originally posted by chiragkhabaria

select name,dob,from programmer
where
dob between getdate() and dateadd(dd,7,getdate())

Chirag, it's their birthdays, not the date they are born.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 08:02:21

why 32 ?? didnt understood??

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:04:10

Because there are a maximum of 31 days every month.
Say it's 24th of August. 32 * 8 + 24 = 280. Sixe days in the future is 32 * 8 + 30 = 286.

Now look for all dob where month and date falls in that range too! This is to disregard the year factor.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:05:45

You can multiply with 100 too! It doesn't matter as long as the multiplier is greater than 31.

100 * Month + Day for 24th of August is 824. Six days in the future is 830.

Peter Larsson
Helsingborg, Sweden

crazyme
Starting Member

11 Posts

Posted - 2006-08-24 : 08:05:49

Peso,

That was a quick turnaround, but getting error

"Incorrect syntax near ')'."

Knowledge grows when shared...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 08:06:03

quote:

Chirag, it's they birthday, not the date they are born.

opps


select name,dob from programmer
where
Month(dob)= Month(getdate())  and 
Day(dob) Between Day(Getdate()) And Day(dateadd(dd,7,getdate())

Chirag

crazyme
Starting Member

11 Posts

Posted - 2006-08-24 : 08:07:01

Forgot to add, when ever you execute the query it should give the person who are celebrating their birthday within a week.(Not specific to any month)...

Knowledge grows when shared...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:07:26

Chirag, what happens when you break months?

Say 29th august to 4th of September?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:10:49

Try this one.

SELECT	*
FROM	Programmer
WHERE	100 * MONTH(dob) + DAY(dob) BETWEEN 100 * MONTH(getdate()) + DAY(getdate()) AND 100 * MONTH(DATEADD(day, 6, getdate())) + DAY(DATEADD(day, 6, getdate()))

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:12:25

quote:
Originally posted by crazyme

Forgot to add, when ever you execute the query it should give the person who are celebrating their birthday within a week.(Not specific to any month)...

It doesn't.
The GETDATE() is todays date.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:13:37

quote:

Originally posted by Peso

SELECT	*
FROM	Programmer
WHERE	32 * MONTH(dob) + DAY(dob) BETWEEN 32 * MONTH(DATEADD(day, 6, getdate())) + DAY(DATEADD(day, 6, getdate()))

Nice one. Between what?

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-24 : 10:21:30

Using my age function from the script library,
this finds birthdays in the next week, starting tomorrow.

Basically, if the number of years old changes in that
date range, your birthday falls in that date range.

It's fairly easy to adapt this for any date range you want.


select
	dob
from
	(
	select dob = convert(datetime,'20000824') union all
	select dob = convert(datetime,'20000825') union all
	select dob = convert(datetime,'20000831') union all
	select dob = convert(datetime,'20000901')
	) a
where
	--Age Function F_AGE_YYYY_MM_DD:
	--http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
	-- Years old today
	left(dbo.F_AGE_YYYY_MM_DD(dob, getdate()),4) <>
	-- Years old at end of range
	left(dbo.F_AGE_YYYY_MM_DD(dob, getdate()+7),4)

Results:


dob                                                    
------------------------------------------------------ 
2000-08-25 00:00:00.000
2000-08-31 00:00:00.000

(2 row(s) affected)

CODO ERGO SUM

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-26 : 03:00:04

quote:
Originally posted by Peso

Chirag, what happens when you break months?

Say 29th august to 4th of September?

Peter Larsson
Helsingborg, Sweden

opps...

It was a weekend over here and girl friend was waiting and as well as shouting

Chirag

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-08-26 : 08:10:29

[code]
CREATE TABLE calendar (
calendarID int IDENTITY (1, 1) NOT NULL ,
calendarDate smallDateTime not NULL ,
yearToDate smallint,
...<other columns like weekday and so>
CONSTRAINT PK_calendar PRIMARY KEY CLUSTERED
(
calendarID
)
)
[/code]
Then populate table, make an index on yearToDate and use it for your query. Of course you'll need an index on calendarDate for other queries.

Masum7
Starting Member

33 Posts

Posted - 2006-08-26 : 12:06:54

quote:

Originally posted by Peso

Try this one.

SELECT	*
FROM	Programmer
WHERE	100 * MONTH(dob) + DAY(dob) BETWEEN 100 * MONTH(getdate()) + DAY(getdate()) AND 100 * MONTH(DATEADD(day, 6, getdate())) + DAY(DATEADD(day, 6, getdate()))

Peter Larsson
Helsingborg, Sweden

what happen larsson if year breaks?
will ur query work then? for exmple dob is '01-01-84' and currentdate is '30-12-06' [between 1230 and 105??]

Masum

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-27 : 23:16:27

quote:
Originally posted by Masum7

quote:
Originally posted by Peso

Try this one.
SELECT *
FROM Programmer
WHERE 100 * MONTH(dob) + DAY(dob) BETWEEN 100 * MONTH(getdate()) + DAY(getdate()) AND 100 * MONTH(DATEADD(day, 6, getdate())) + DAY(DATEADD(day, 6, getdate()))

Peter Larsson
Helsingborg, Sweden

what happen larsson if year breaks?
will ur query work then? for exmple dob is '01-01-84' and currentdate is '30-12-06' [between 1230 and 105??]

Masum

Doesn't work at end of year.


declare @today datetime
set @today = '20061227'

select	
	*
from
	(
	select dob = convert(datetime,'20001224') union all
	select dob = convert(datetime,'20001225') union all
	select dob = convert(datetime,'20001226') union all
	select dob = convert(datetime,'20001227') union all
	select dob = convert(datetime,'20001228') union all
	select dob = convert(datetime,'20001229') union all
	select dob = convert(datetime,'20001230') union all
	select dob = convert(datetime,'20001231') union all
	select dob = convert(datetime,'20010101') union all
	select dob = convert(datetime,'20010102') union all
	select dob = convert(datetime,'20010103') union all
	select dob = convert(datetime,'20010104') union all
	select dob = convert(datetime,'20010105') union all
	select dob = convert(datetime,'20010106')
	) a
where
	100 * month(dob) + day(dob) between
	100 * month(@today) + day(@today) and 
	100 * month(dateadd(day, 6, @today)) + day(dateadd(day, 6, @today))

Results:


dob                                                    
------------------------------------------------------ 

(0 row(s) affected)

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-27 : 23:28:47

Here is another solution that works without the UDF F_AGE_YYYY_MM_DD used in my first post.

Notice that is handles both end of year and leap year correctly. The second result set has 8 rows, because it includes people born on Feb 29.


declare @today datetime
set @today = '20061227'

select	
	*
from
	(
	select dob = convert(datetime,'20001224') union all
	select dob = convert(datetime,'20001225') union all
	select dob = convert(datetime,'20001226') union all
	select dob = convert(datetime,'20001227') union all
	select dob = convert(datetime,'20001228') union all
	select dob = convert(datetime,'20001229') union all
	select dob = convert(datetime,'20001230') union all
	select dob = convert(datetime,'20001231') union all
	select dob = convert(datetime,'20010101') union all
	select dob = convert(datetime,'20010102') union all
	select dob = convert(datetime,'20010103') union all
	select dob = convert(datetime,'20010104') union all
	select dob = convert(datetime,'20010105') union all
	select dob = convert(datetime,'20010106')
	) a
where
	-- Find next birthday after today
	case
	when	dateadd(yy,datediff(yy,dob,@today),dob) >
		dateadd(dd,datediff(dd,0,@today),0)
	then dateadd(yy,datediff(yy,dob,@today),dob)
	else  dateadd(yy,datediff(yy,dob,@today)+1,dob)
	end  between
	-- Tomorrow
	dateadd(dd,datediff(dd,0,@today)+1,0) and
	-- Tomorrow plus 6 days
	dateadd(dd,datediff(dd,0,@today)+7,0)


set @today = '20060226'
select	
	*
from
	(
	select dob = convert(datetime,'20000224') union all
	select dob = convert(datetime,'20000225') union all
	select dob = convert(datetime,'20000226') union all
	select dob = convert(datetime,'20000227') union all
	select dob = convert(datetime,'20000228') union all
	select dob = convert(datetime,'20000229') union all
	select dob = convert(datetime,'20000301') union all
	select dob = convert(datetime,'20000302') union all
	select dob = convert(datetime,'20000303') union all
	select dob = convert(datetime,'20000304') union all
	select dob = convert(datetime,'20000305') union all
	select dob = convert(datetime,'20000306')
	) a
where
	-- Find next birthday after today
	case
	when	dateadd(yy,datediff(yy,dob,@today),dob) >
		dateadd(dd,datediff(dd,0,@today),0)
	then dateadd(yy,datediff(yy,dob,@today),dob)
	else  dateadd(yy,datediff(yy,dob,@today)+1,dob)
	end  between
	-- Tomorrow
	dateadd(dd,datediff(dd,0,@today)+1,0) and
	-- Tomorrow plus 6 days
	dateadd(dd,datediff(dd,0,@today)+7,0)

Results:


dob                                                    
------------------------------------------------------ 
2000-12-28 00:00:00.000
2000-12-29 00:00:00.000
2000-12-30 00:00:00.000
2000-12-31 00:00:00.000
2001-01-01 00:00:00.000
2001-01-02 00:00:00.000
2001-01-03 00:00:00.000

(7 row(s) affected)

dob                                                    
------------------------------------------------------ 
2000-02-27 00:00:00.000
2000-02-28 00:00:00.000
2000-02-29 00:00:00.000
2000-03-01 00:00:00.000
2000-03-02 00:00:00.000
2000-03-03 00:00:00.000
2000-03-04 00:00:00.000
2000-03-05 00:00:00.000

(8 row(s) affected)

CODO ERGO SUM

Masum7
Starting Member

33 Posts

Posted - 2006-08-28 : 03:51:21

ok.. Michael Valentine Jones.. it works nicely.. welldone

Masum

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources