Solved

# SQL Server Age calculation

Posted on 2005-05-02
Medium Priority
436 Views
Hello all I am moving an MS Access app to SQL Server

I have several append queries that calculate age.

I am having some problems with this.  I keep getting that Age, Date, and Nz are not recognized functions...

Original Query

INSERT INTO Person_RANK3_AGE_RX ( Person_ID )
SELECT Person_PHARM_TABLE.Person_ID
FROM Person_TABLE INNER JOIN ((DRUGS INNER JOIN Person_PHARM_TABLE ON DRUGS.DRUG_ID = Person_PHARM_TABLE.DRUG_ID) INNER JOIN AVOID_DRUGS ON DRUGS.DRUG_DESC = AVOID_DRUGS.Medicine_Name) ON Person_TABLE.Person_ID = PATIENT_PHARM_TABLE.Person_ID
GROUP BY Person_PHARM_TABLE.Person_ID, DRUGS.DRUG_ID;

What this query does is it looks at a risk associated with taking a specific drug frm the AVOID_DRUGs table.  and then only looks for peopel older than 64.  It retrieves the DOB from the Person_table.

Should I use something similar to Datediff...?

0
Question by:cubic22
• 7
• 4
• 4

LVL 70

Expert Comment

ID: 13909463
Yes, in place of AGE() use DATEDIFF().  Not sure what "Nz" is, but my best guess is it checks for NULL; if so, it would be replaced by ISNULL(column, value-if-null).
0

LVL 70

Expert Comment

ID: 13909501
Hmm, even less sure about nz, but here goes; for example:

AND (DATEDIFF(YEAR, ISNULL([Person_TABLE].[DOB], GETDATE()), GETDATE()) > 64)
0

LVL 14

Accepted Solution

ID: 13909518
DateDiff(yy, date1, date2) returns the diff of 2 dates in years, but it does not take into effect when the date of those 2 dates, it soply subrtacts the 2 years.  So Dec 2 2000 to Dec 1 2005 would be 5 year. for are, there not 5 years old until the next day

Try using this UDF

CREATE FUNCTION dbo.UFun_CMPC_AgeAtDate
(
@DOB AS DATETIME,
@ReferenceDate AS DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @age_in_years AS INT

SET @age_in_years = DATEDIFF(yy, @DOB, @ReferenceDate) +
CASE WHEN (DATEPART(Month, @ReferenceDate) < DATEPART(Month, @DOB)) THEN -1
WHEN (DATEPART(Month, @ReferenceDate) > DATEPART(Month, @DOB)) THEN 0
WHEN (DATEPART(Day, @ReferenceDate) < DATEPART(Day, @DOB)) THEN -1
ELSE 0 END

RETURN @age_in_years
END
0

LVL 70

Expert Comment

ID: 13909530
CORRECTION:

AND (DATEDIFF(YEAR, ISNULL([Person_TABLE].[DOB], GETDATE()), GETDATE()) - CASE WHEN DATEPART(DAYOFYEAR, [Person_TABLE].[DOB]) <= DATEPART(DAYOFYEAR, GETDATE()) THEN 0 ELSE 1 END > 64)
0

LVL 14

Expert Comment

ID: 13909581
Scott's correction works with the exception of a Leapyear, Feb 29th, April 1st have the same DAYOFYEAR when one is a leapyear, and one is not.
0

LVL 14

Expert Comment

ID: 13909596
AND (dbo.UFun_CMPC_AgeAtDate(ISNULL([Person_TABLE].[DOB], GETDATE()), GETDATE()) > 64)
0

LVL 70

Expert Comment

ID: 13909644
D'OH, yeah, forgot about leap years.  Was trying to avoid string comparisons, but can't, I guess:

AND (DATEDIFF(YEAR, ISNULL([Person_TABLE].[DOB], GETDATE()), GETDATE()) - CASE WHEN CONVERT(CHAR(5), GETDATE(), 1) >= CONVERT(CHAR(5), [Person_TABLE].[DOB], 1) THEN 0 ELSE 1 END > 64)
0

LVL 70

Expert Comment

ID: 13909665
OOH, thinking more clearly now since I just had breakfast :-), the *much* better way to do this is:

WHERE (((AVOID_DRUGS.RISK)=3) AND ([Person_TABLE].[DOB] <= DATEADD(YEAR, -64, GETDATE())
0

LVL 70

Expert Comment

ID: 13909684
This allows the table column to be "pure" (no functions performed against it), which will allow an index to be used.  Also, the other calculation is much easier and only has to be performed once.
0

LVL 14

Expert Comment

ID: 13909735
I agree with Scott, this is your best option for determining of someone is under a given age.

WHERE (((AVOID_DRUGS.RISK)=3) AND ([Person_TABLE].[DOB] <= DATEADD(YEAR, -64, GETDATE())

This would make a good wheaties commercial
0

Author Comment

ID: 13910607
Well shoot....I just commented and accepted.... DOH...I forgot to refresh the browser first
.....
0

Author Comment

ID: 13910618
What should I do now?....
0

LVL 70

Expert Comment

ID: 13910817
That's up to you.  If you want to, you could request cancelling the acceptance and selecting a different split/answer.  If so, just post a follow-up request here and I will take care of it.  Or, of course, you could leave as is if you are comfortable with the current status.
0

Author Comment

ID: 13918049
Let me test out the new way and I will get back to yall.
0

Author Comment

ID: 13918447
If its not too much trouble I would like to request a cancel so I can split the points.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month17 days, 5 hours left to enroll