Look at DateOfBirth and see their CurrentBirthday

[slobad23]

I am using MSSQL Server 2005.

I have this calculation for a field called "CurrentBirthday".

As an example, I have 2 employees

Name:John, DOB:15-09-1988
Name:Bill, DOB:15-09-1989

CurrentBirthday for John shows 15-09-2011
CurrentBirthday for Bill shows 15-09-2012

I can see that this is because John's DOB is on a leap year, but I cannot for the life of my figure out a way around this to mean that they both show 15-09-2012...

My calculation:

CASE WHEN
DATEPART(DY,DATEOFBIRTH) >DATEPART(DY,GETDATE())
THEN
(CASE WHEN
(DATEPART(DD,DATEOFBIRTH) = '29' AND DATEPART(MM,DATEOFBIRTH) = 2)
THEN
(CAST(
(
STR( YEAR( GETDATE() ) ) + '/02/28'
)
AS DATETIME ) )
ELSE
(CAST(
(
STR( YEAR( GETDATE() ) ) + '/' +
STR( MONTH( DATEOFBIRTH ) ) + '/' +
STR( DAY( DATEOFBIRTH ) )
)
AS DATETIME ))
END)
ELSE
(CASE WHEN
(DATEPART(DD,DATEOFBIRTH) = '29' AND DATEPART(MM,DATEOFBIRTH) = 2)
THEN
(CAST(
(
STR( YEAR( DATEADD(YY,1,GETDATE() ) ) ) + '/02/28'
)
AS DATETIME ) )
ELSE
(CAST(
(
STR( YEAR( DATEADD(YY,1,GETDATE() ) ) )+ '/' +
STR( MONTH( DATEOFBIRTH ) ) + '/' +
STR( DAY( DATEOFBIRTH ) )
)
AS DATETIME ))
END)
END

 

[gmmastros]

I think you are making this a lot harder than it has to be.

There are a lot of date functions in SQL Server, and they all work (and respect) leap years.

For example, 2004 is a leap year. If you add 1 year to Feb 29, 2004, you get Feb 28, 2005. If you add 4 year, it will be another leap year, so you get the 29th.

Ex:

Select	DateAdd(Year, 1, '20040229')
Select	DateAdd(Year, 4, '20040229')

Therefore, to see what day a person should celebrate their birthday, you simply need to add the appropriate number of years.

Here is an example that uses a table variable so we can hard code some data to test with. Copy/paste the code below to a query window to see how this works.

Declare @Temp Table(Name VarChar(20), DATEOFBIRTH DateTime)

Insert Into @Temp Values('John','19880915')
Insert Into @Temp Values('Bill','19890915')
Insert Into @Temp Values('Before Leap','20040228')
Insert Into @Temp Values('Leap','20040229')
Insert Into @Temp Values('Not Leap','20050228')
Insert Into @Temp Values('Another','19701228')

Select Name,
       DateOfBirth,
       DateAdd(Year, DateDiff(Year, DateOfBirth, GetDate()), DateOfBirth) As DateCelebrated
From   @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[slobad23]

This does not achieve the results according to leap years correctly.

If I have someone with a date of birth that is a leap year, their currentbirthday field with your calculation shows differently than someone who has a year in their date of birth which is not a leap year.

Example:

Name:John, DOB:16-09-1988
Name:Bill, DOB:16-09-1989
CurrentBirthday for John shows 16-09-2011
CurrentBirthday for Bill shows 16-09-2012

 

[PWise]

Name:John, DOB:16-09-1988
Name:Bill, DOB:16-09-1989
CurrentBirthday for John shows 16-09-2011
CurrentBirthday for Bill shows 16-09-2012
[/qoute]

And What should it be

 

[slobad23]

Name:John, DOB:16-09-1988
Name:Bill, DOB:16-09-1989
CurrentBirthday for John shows 16-09-2011
CurrentBirthday for Bill shows 16-09-2012

Both of them are born on the same day... the results should both show 16-09-2012.

 

[gmmastros]

When you run my query, it does show the same "DateCelebrated" for both. I'm a little confused.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[drlex]

George,
I think slobad23 wants the query to return the next birthday for each person.
So, amending your code with a conditional on the date

Declare @Temp Table(Name VarChar(20), DATEOFBIRTH DateTime)

Insert Into @Temp Values('John','19880915')
Insert Into @Temp Values('Bill','19890915')
Insert Into @Temp Values('Before Leap','20040228')
Insert Into @Temp Values('Leap','20040229')
Insert Into @Temp Values('Not Leap','20050228')
Insert Into @Temp Values('Another','19701228')

Select Name,
       DateOfBirth,
       CASE WHEN DateAdd(Year, DateDiff(Year, DateOfBirth, GetDate()), DateOfBirth) < getdate() THEN
DateAdd(Year, 1+DateDiff(Year, DateOfBirth, GetDate()), DateOfBirth) ELSE
DateAdd(Year, DateDiff(Year, DateOfBirth, GetDate()), DateOfBirth) END As NextBirthday 
From   @Temp

soi là, soi carré

 

[gmmastros]

You may be right. My query would show that date that a birthday is celebrated on for the current year. Your query would show the date when the birthday is next celebrated.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[slobad23]

That worked perfectly.

Thank you very much!