Solved

SQL Query question

Posted on 2013-01-17
2
270 Views
Last Modified: 2013-01-17
Good day. I am trying to use the following to find the Age as of a specific date (11/1/12). It doesn't seem to work for all. On some, the age does match what it should be but on some it adds an extra year.

DOB field contains dates as "1997-06-06 00:00:00.000"

The query is CAST(Round((DateDiff(d,id.DOB,'2012-11-01')/365.25),0)  as INT) as AGE

For instance, I have one record where the DOB field is "1995-05-01 00:00:00.000" and the Age comes out as 18 but should be 17.

Any idea how I can get it so that it provides the proper age as of the date requested?
0
Comment
Question by:mig1980
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 39

Accepted Solution

by:
appari earned 250 total points
ID: 38789981
that was because of round function you used.
use floor function as follows
CAST(floor((DateDiff(d,id.DOB,'2012-11-01')/365.25))  as INT) as AGE
0
 

Author Comment

by:mig1980
ID: 38790039
Perfect. Thank you very much!!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 shrink a transaction log file down to a reasonable size.

630 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