What does this code snippet mean?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

What does this code snippet mean??

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-09-07 : 21:31:06

BirthDate and StartDate get passed in as smalldatetime values...


SET @age = (DATEDIFF(YY, @BirthDate, @StartDate)
             - CASE WHEN (MONTH(@BirthDate) = MONTH(@StartDate) AND DAY(@BirthDate) > DAY(@StartDate)
                         OR MONTH(@BirthDate) > MONTH(@StartDate))  THEN 1 ELSE 0 END)

RETURN @age

I'm not certain what the THEN 1 ELSE 0 END part means. If someone could shed some light on me that would help out a lot.
--Nick

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-07 : 21:49:47

The case statement is there to subtract one year if they have not yet reached their birthday this year and subtract zero if they have. This is needed because the DATEDIFF function returns the difference in year boundries crossed, not full years.

You xan see very similar logic in this code snippet from my Age Function in the script library that calculates age in years, months, and days.


select
	AGE_IN_YEARS =
	case
	when AnniversaryThisYear <= END_DATE
	then datediff(yy,START_DATE,END_DATE)
	else datediff(yy,START_DATE,END_DATE)-1
	end

Age Function F_AGE_YYYY_MM_DD:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

CODO ERGO SUM

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-09-08 : 13:41:40

Ahhh, hence the '-' sign at the beginning of the CASE statement there, got it, thanks. I've also used that age function on several occasions :-)
--Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources