Need reassurances - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Need reassurances

Author

Topic

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-22 : 10:51:26

I'm curious as to whether this function will give me problems, like during a leapyear, etc. Please assess it for me.

CREATE FUNCTION dbo.fnCalcAge
(
@DOB AS DATETIME,
@TODAY AS DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @AGE AS INT
IF (DATEPART (dayofyear, @DOB) <= DATEPART(dayofyear, @TODAY))
BEGIN
SELECT @AGE = DATEDIFF(YEAR, @DOB, @TODAY)
END
ELSE
IF (DATEPART(dayofyear,@DOB)>DATEPART(dayofyear,@TODAY))
BEGIN
SELECT @AGE = (DATEDIFF(YEAR,@DOB,@TODAY) -1)
END
RETURN @AGE
END

Thanks in advance!

----------------
-Stephen

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-22 : 11:05:22

Oh, I'm also always interested in more elegant code, or better efficiency.

----------------
-Stephen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 11:13:00

I haven't looked at the code, but here's a good way to test it (brute force, not elegant at all, but you know you haven't overlooked anything either

)

Get this function - it returns a table of all the dates in a range
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Then use a query like this to get the ages from your function

select [Year], dbo.fnCalcAge([Date], '12/31/2007') from dbo.f_table_date('1/1/1900', '1/1/2006')
group by [Year], dbo.fnCalcAge([Date], '12/31/2007')

This will calculate the age on 12/31/2007 for every date since 1/1/1900. It groups by age and year so it should return one row for each year. But it doesn't (in 1984 for example, it has two different ages - so there's a problem with your code - I leave you to find it

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 11:33:55

There is a working one here if you don't want to hassle with it yourself

http://www.sql-server-helper.com/functions/get-age.aspx

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-22 : 11:50:32

Ummm, gee... That function is CRAZY (but neato). Somebody either had way too much time on their hands, or was going to be in the unemployment line if he or she didn't test his or her date function for all possible scenarios. (MIL-SPEC, maybe?)

I think I'll take door number two, Monty. Lazy, I know. I thought maybe that dayofyear thing might cause problems, which is why I asked. It seemed so simple, but it just never is, huh?

----------------
-Stephen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 11:55:42

Actually that date table function is much more useful than just for testing - you often have to write queries that return data for a range of dates and match events (I use the term events very loosely, an order is an event for instance).

The problem is that you don't usually have events for every date, so there are missing dates in your query. If you want every date in your query results, you can use that table function and join your event data to it - sweet!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources