Birthday Query

Hi, i have a table users which contains a birthday field, i would like to have a query that displays the users birthdays for the coming 7 days. Can someone help me with this.
Thanx allot.
Wimmoselect birthday,username from table where convert(datetime,convert(varchar(10),birthday,101) ,101) between convert(datetime,convert(varchar(10),getdate(),101 ),101) and convert(datetime,convert(varchar(10),dateadd(dd,7, getdate()),101),101)

A very rough cut .. but i think it will work|||if this column is datetime, then you can use difference of getdate and datediff function for wanted result.|||Hi Enigma thanx for your reply, your query works but only when the year of birth is the current year|||My Bad ...|||http://www.dbforums.com/showthread.php?threadid=985381 for a similar thread and reference.

HTH|||http://www.dbforums.com/showthread.php?threadid=985381 for a similar thread and reference.

HTH
I tried that solution but it didn't work for me|||How can i change your query so that it dont look @. the year but only to the day & month|||WHERE DATEDIFF(dd,GetDate(),Birthdate) <= 7?|||Ooopps...

DECLARE @.x datetime, @.y datetime
SELECT @.x = GetDate(), @.y = '05/6/2004'
SET @.x = '1900/'+CONVERT(varchar(2),DATEPART(mm,@.x))+'/'+CONVERT(varchar(2),DATEPART(dd,@.x))
SET @.y = '1900/'+CONVERT(varchar(2),DATEPART(mm,@.y))+'/'+CONVERT(varchar(2),DATEPART(dd,@.y))

SELECT DATEDIFF(dd,@.x,@.y)

Did you look at the function?

That should definetly work for you...|||Thanx for your response but i do not completely understand what you are saying here|||I am trying to get the function work but i do not know what i am doing wrong|||Try using:-- ptp 20040427 Compute days to next anniversary

CREATE FUNCTION dbo.fDaysToAnniversary(
@.pdAnn DATETIME -- Base Date
, @.pdNow DATETIME -- Target date, usually today
) RETURNS SMALLINT AS BEGIN

DECLARE @.dNext DATETIME -- Next anniversary

SET @.dNext = DateAdd(year, DateDiff(year, @.pdAnn, @.pdNow), @.pdAnn) -- First guess
IF @.dNext < @.pdNow SET @.dNext = DateAdd(year, 1, @.dNext) -- Bump if already past

RETURN DateDiff(day, @.pdNow, @.dNext)
END
GOThis function takes two arguments, one is a date you wish to project an anniversary for, and the other is the date you want to figure the days from. You'd typically use it something like:SELECT dbo.fDaysToAnniversary('2001-09-11', GetDate())-PatP|||http://r937.com/sql.cfm|||Cut and paste this...I think there was a type-o in the original code...should execute no proble in QA

USE Northwind
GO

CREATE FUNCTION udf_DaysToBDay(@.Bday datetime, @.Dateuntil datetime)
RETURNS int
AS
BEGIN
DECLARE @.x int, @.ModifyBDay datetime

SET @.ModifyBDay = CONVERT(datetime,
CONVERT(char(4),year(@.Dateuntil))
+ '/'
+ CONVERT(char(5),@.Bday,101)
)

SET @.x = datediff(dd, @.Dateuntil, @.ModifyBDay)

RETURN @.x
END

GO

SELECT dbo.udf_DaysToBDay('10/24/1960',GetDate())

SELECT dbo.udf_DaysToBDay(BirthDate,GetDate()) FROM Employees
GO

DROP FUNCTION udf_DaysToBDay
GO|||Just an observation, but my function looks at the problem differently than Brett's does. My function returns the number of days (0-366) until the next anniversary of a given date. Brett's function returns the number of days (-365 to 365) to/from the anniversary of that data this year.

You'll need to decide which works better for you. If you need to be able to work across year boundaries to find upcoming birthdays, I think you'll be better off using mine.

-PatP|||Thanx Guys to all of you, i tried the things that you guys proposed, the thing that workes for me is the 1 from Blindman, I really appreciate the time that all of you spend on my problems.

Thanx Wimmo|||Good point...

They year however is based on whatever the date supplied is...just did that because getdate() is non determenistic...

And yes, I would assume (based on the fact they wanted 7 days) that for Christmas day and forward, mine would be incorrect... :eek: