BirthdayList for next seven 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)

BirthdayList for next seven days

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-10-11 : 09:49:31

I need an sql stored proc query that gets a list of birthdays in the next 7 days.

Member Table
- Name varchar(50)
- BirtDate smalldatetime

Output should look like

John, Monday October 11
Peter, Monday October 11
Jack, Tuesday October 12
etc

Thanks

Rika1976
Starting Member

5 Posts

Posted - 2004-10-11 : 10:04:50

Try this. The between statement gets converted to varchar then back to smalldatetime because it gets rid of any of the time restraints used in capturing the data and the getdate function.
Thanks,
Erika

select [name], + ', ' + datename(weekday,BirtDate) + ' ' + datename (month,BirtDate) + ' ' + datename(day,BirtDate)
from [table name]
where cast(cast(BirtDate as varchar(11))as smalldatetime) between cast(cast(getdate() as varchar(11))as smalldatetime) and (cast(cast(getdate() as varchar(11))as smalldatetime) + 7)

adlo
Posting Yak Master

108 Posts

Posted - 2004-10-11 : 10:29:35

Thanks

I have found the follwing select part.
Still have to test it.

datediff
(
dd
,convert(datetime,'1900/'+cast(month(getdate()) as varchar)+'/'+cast (day(getdate()) as varchar),111)
,convert(datetime,'1900/'+cast(month(BirthDate) as varchar)+'/'+cast (day(BirthDate) as varchar),111)
) between 0 and 7

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 10:44:51

this should be considerably faster:

where cast(BirthDate as smalldatetime) <= dateadd(d, 7, getdate())

Go with the flow & have fun! Else fight the flow

adlo
Posting Yak Master

108 Posts

Posted - 2004-10-11 : 11:03:25

Thanks,
but I need a Birthday list and not a BirthDate list.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 11:16:30

i don't think you understood what i meant:

select name + ', ' + datename(weekday,BirtDate) + ' ' + datename (month,BirtDate) + ' ' + datename(day,BirtDate)
from Member
where BirthDate <= dateadd(d, 7, getdate()) -- this is for next 7 days

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources