Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

New Function Expand / Collapse
Author
Message
Posted Sunday, November 04, 2012 5:41 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
Jeff,

I see a couple of minor issues with the function:
1. Results may be a little off if the @DOB and @Now are not exactly set to 00:00:00, so I recommend rounding them back to midnight. This would only be an issue on their birthday.
2. Results are a little odd if @Now is less than @DOB, so I recommend returning a NULL in that case. The concept of negative age doesn't seem to have any real world meaning anyway. And yes, I left this out of the code I posted also.

Alternate version with suggested changes:
CREATE FUNCTION dbo.AgeInYears_mvj
(
@DOB DATETIME, --Date of birth or date of manufacture
@Now DATETIME --Usually, GETDATE() or CURRENT_TIMESTAMP but
--can be any date source like a column.
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT AgeInYears =
CASE
WHEN a.[Now] < a.[DOB]
THEN null
--If birthday hasn't happended yet this year, subtract 1.
WHEN DATEADD(yy, DATEDIFF(yy, a.[DOB], a.[Now]), a.[DOB]) > a.[Now]
THEN DATEDIFF(yy, a.[DOB], a.[Now]) - 1
ELSE DATEDIFF(yy, a.[DOB], a.[Now])
END
FROM
(SELECT [DOB] = dateadd(dd,datediff(dd,0,@DOB),0),
[Now] = dateadd(dd,datediff(dd,0,@Now),0)) a
;
GO

SELECT * FROM dbo.AgeInYears_MVJ('19601104 01:00','20121104')
SELECT * FROM dbo.AgeInYears ('19601104 01:00','20121104')

SELECT * FROM dbo.AgeInYears_MVJ('19601104','19601103')
SELECT * FROM dbo.AgeInYears ('19601104','19601103')

Results:
AgeInYears
-----------
52

AgeInYears
-----------
51

AgeInYears
-----------
NULL

AgeInYears
-----------
-1
Post #1380856
Posted Sunday, November 04, 2012 8:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
Michael Valentine Jones (11/4/2012)
Jeff,

I see a couple of minor issues with the function:


Thanks for catching the mistake on my part , Michael. Thank you, also, for the corrected code which will work even i SQL Server 2000.

I deleted the code in my previous post because I didn't want anyone to use it by mistake for the very reasons you've given.


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

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1380859
Posted Monday, November 05, 2012 3:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
I don't think that function should worry about time part of date of birth. In SQL2008 you better just to use DATE type so the time wouldn't be present. Pre-2008 I would enforce that DOB DATETIME column would only contain date part, so I wouldn't worry about time in queries which uses it.
Actually, I'm not even sure that UDF is really required here... I would probaley just use in-line cross apply, something like that:

      -- here is a sample table with DOB as DATE only
DECLARE @MySample TABLE (DOB DATE)
INSERT @MySample
VALUES ('5 Nov 2012'),('3 Nov 2014'),('5 Nov 2011'),('6 Nov 2011'),('4 Apr 1973')

-- usually it would be single day and most likely it will be today...
DECLARE @DateToday DATE = GETDATE()

SELECT M.DOB
,AC.AGE
FROM @MySample M
CROSS APPLY (SELECT DATEDIFF(yy, M.DOB, @DateToday) -
CASE WHEN DOB > @DateToday THEN NULL
WHEN DATEADD(yy, DATEDIFF(yy, DOB, @DateToday), DOB) > @DateToday
THEN 1 ELSE 0
END AGE
) AC


Actually, if you want to calculate the AGE on the range of dates, you can use the following:

      -- here is a sample table with DOB as DATE only
DECLARE @MySample TABLE (DOB DATE)
INSERT @MySample
VALUES ('5 Nov 2012'),('3 Nov 2014'),('5 Nov 2011'),('6 Nov 2011'),('4 Apr 1973')

-- Range of dates:
DECLARE @Dates TABLE (OnDay DATE)
INSERT @Dates
VALUES ('5 Nov 2012'),('10 Nov 2020'),('5 Nov 1970'),('5 Nov 2011'),('4 Apr 2073')

SELECT M.DOB
,D.OnDay
,AC.AGE
FROM @MySample M
CROSS JOIN @Dates D
CROSS APPLY (SELECT DATEDIFF(yy, M.DOB, D.OnDay) -
CASE WHEN DOB > D.OnDay THEN NULL
WHEN DATEADD(yy, DATEDIFF(yy, DOB, D.OnDay), DOB) > D.OnDay
THEN 1 ELSE 0
END AGE
) AC
ORDER BY M.DOB, D.OnDay



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1380955
Posted Monday, November 05, 2012 5:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
Ordinarily, I'd agree, Eugene. One of the reasons why I put these types of things in functions is because of the "problem" with leap year dates. As Michael stated, the current function uses an aniversary date of Feb 28th for Feb 29th on non-leap years. If someone decides that it should really be Mar 1st, I only have one spot to go to to fix things instead of trying to find all the places where I may have used inline code.

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

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1381026
Posted Monday, November 05, 2012 7:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
Jeff Moden (11/3/2012)
Sean Lange (11/2/2012)
CELKO (11/2/2012)
If my birthday is in December (1983-12-12) and I just did the datediff function using CURRENT_TIMESTAMP and that birthdate...It would return that my age is 29 when really I would still be 28! That is why I have all those if statements! Because those account for those situations!


We have a DATE data type, the ANSI/ISO Standard CURRENT_TIMESTAMP, and we use the ISO-8601 date formats; I corrected your posting. Now it gets worse; which age system do you use? Asians count the year in which you are living (I am in my 66-th year) and Westerners count the last whole year you passed (I was 65 on 2012-01-24).

DATEDIFF (YEAR, '1983-12-12', CAST(CURRENT_TIMESTAMP AS DATE)) = 29

But:
SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65

Instead of all that casting and concatenation, you can use:

DATEDIFF (YEAR, @dd, CAST(CURRENT_TIMESTAMP AS DATE)),
CASE SIGN(MONTH(CURRENT_TIMESTAMP) - MONTH (@dd))
WHEN -1 THEN -1 ELSE 0 END

Little puzzle: replace the CASE expression with calls to SIGN() and ABS().


Of if you prefer the really simple method you can just get the months and do integer division.

declare @dd datetime = '1947-2-24'
select datediff(month, @dd, CURRENT_TIMESTAMP)/12



Nice try Sean, but it doesn't work in all cases. Please see the following.

DECLARE @DOB DATETIME
SET @DOB = '2008-12-31'
DECLARE @Now DATETIME
SET @Now = '2009-12-30'

select datediff(month, @DOB, @Now)/12



DOH! I figured it was far to simple to work.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1381079
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse