?
Solved

SQL Server Age calculation

Posted on 2005-05-02
15
Medium Priority
?
436 Views
Last Modified: 2006-11-18
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
WHERE (((AVOID_DRUGS.RISK)=3) AND ((Age(Nz([Person_TABLE].[DOB],DateAdd("yyyy",1,Date()))))>64))
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
Comment
Question by:cubic22
  • 7
  • 4
  • 4
15 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
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

by:Scott Pletcher
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

by:
adwiseman earned 500 total points
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 70

Expert Comment

by:Scott Pletcher
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

by:adwiseman
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

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

Expert Comment

by:Scott Pletcher
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

by:Scott Pletcher
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

by:Scott Pletcher
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

by:adwiseman
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

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

Author Comment

by:cubic22
ID: 13910618
What should I do now?....
0
 
LVL 70

Expert Comment

by:Scott Pletcher
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

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

Author Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question