RE: Interesting Case Statement 1

[allyne]

Hello,

I have a case statement that calculates the age of a person as of 20090630. This is all hard coded and looks like this:
Age = Case When CASE WHEN dateadd(year, datediff(year, date_of_birth, '20090630'),
date_of_birth) > '20090630'
THEN datediff(year, date_of_birth, '20090630') - 1 ELSE datediff(year, date_of_birth, '20090630') END <1 then 0
ELSE CASE WHEN dateadd(year, datediff(year, date_of_birth, '20090630'),
date_of_birth) > '20090630'
THEN datediff(year, date_of_birth, '20090630') - 1 ELSE datediff(year, date_of_birth, '20090630') END end

What I would like to do is replace the hard coded date to say calculate the age as of 0630 (06/30) of any year.

Just can't seem to get this to work.....

Thanks for all your help!

 

[Trevoke]

If "Any year" means "The current year", then declare a variable, get today's date, do some string manipulation with today's year and '0630' and you'll end up with a string similar to what you want.

If "Any year" means "Some year", then declare a variable, and do some string manipulation with '0630' and however the "any year" gets passed to your SQL.

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

 

[bborissov]

....
, (YEAR(GETDATE()) - YEAR(DayOfBirth)) -
  CASE WHEN MONTH(DayOfBirth) > MONTH(GETDATE())
            THEN 1
       WHEN MONTH(DayOfBirth) = MONTH(GETDATE()) AND
            DAY(DayOfBirth)   > DAY(GETDATE())
            THEN 1
  ELSE 0 END

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

There was a recent thread on the same topic in MSDN:

http://social.msdn.microsoft.com/Fo...l/thread/44e5689f-a8cc-4b70-aa82-0928f2673d65

 

[allyne]

Hi Everyone,

Thanks for all your responses!!! I'll check out each response and let you know what works the best!

Again thanks!

 

[JeffModen]

Heh... well?

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[allyne]

Hi Everyone

I finally had a chance to get back to this....Tis the Season! This is what I've come up with so far and it seems to work for my purposes....

(YEAR(GETDATE()) - YEAR(date_of_birth)) - CASE WHEN MONTH(date_of_birth) > =6
THEN 1
WHEN MONTH(date_of_birth)> = 6
AND DAY(date_of_birth)>= DAY(GETDATE())
THEN 1
ELSE 0
END

Thanks again for all your help!

 

[bborissov]

So, if the person is born on 7 Aug S/He always will be one year smaller that the person who is born on 7 May?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

)) When is your birthday, Boris?

 

[bborissov]

April, unfortunately

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[allyne]

Ya...I know this is kinda strange but it's part of a criteria in a stored procedure....I wouldn't use this as a real age calculation...

Probably should have explained this earlier...Sorry...