# Thread: How to Query for People Age 70 or Older?

1. Registered User
Join Date
Jan 2009
Posts
46

## Unanswered: How to Query for People Age 70 or Older?

Hey guys,

I am trying to write a query that will return all people who are age 70 or older. The confusing part is I need to see people who have are 70 or older as of 07-01-2009.

My query so far only looks for people who turned 70 within the entire year of 2009. I know I'm close, but just confused on how to query to look at a specific date.

Code:
```SELECT A.MBR_SSN_NBR AS SSN,
A.MBR_F_NM AS FIRST_NAME,
A.MBR_L_NM AS LAST_NAME,
CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,
YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
YEAR(CURRENT DATE) - YEAR(A.MBR_BIRTH_DT) as AGE

FROM DSNP.PR01_T_MBR A,
DSNP.PR01_T_MBR_SYS B
where A.MBR_SSN_NBR = B.MBR_SSN_NBR
and YEAR(A.MBR_BIRTH_DT) <= 1939
and A.MBR_BIRTH_DT <> '01/01/0001'
and B.mbr_stat_cd = '1'```

Can anyone help?

2. Registered User
Join Date
Aug 2009
Posts
16
Try this to see if it works:
SELECT A.MBR_SSN_NBR AS SSN,
A.MBR_F_NM AS FIRST_NAME,
A.MBR_L_NM AS LAST_NAME,
CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,
YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
YEAR(CURRENT DATE) - YEAR(A.MBR_BIRTH_DT) as AGE

FROM DSNP.PR01_T_MBR A,
DSNP.PR01_T_MBR_SYS B
where A.MBR_SSN_NBR = B.MBR_SSN_NBR
and A.MBR_BIRTH_DT < '07/01/1939'
and A.MBR_BIRTH_DT <> '01/01/0001'
and B.mbr_stat_cd = '1'

3. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Why do not use AGE in predicate ?

SELECT A.MBR_SSN_NBR AS SSN,
A.MBR_F_NM AS FIRST_NAME,
A.MBR_L_NM AS LAST_NAME,
CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,
YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
YEAR(CURRENT DATE) - YEAR(A.MBR_BIRTH_DT) as AGE

FROM DSNP.PR01_T_MBR A,
DSNP.PR01_T_MBR_SYS B
where A.MBR_SSN_NBR = B.MBR_SSN_NBR
YEAR(CURRENT DATE) - YEAR(A.MBR_BIRTH_DT) >= 70
and A.MBR_BIRTH_DT > date('0001-01-01')

and B.mbr_stat_cd = '1'
Lenny

4. Registered User
Join Date
Jan 2003
Posts
4,310
Using YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) is not accurate. If a person is born on 12/31/1939 and the current date is 08/31/2009, then your expression will say that that person is 70 when in fact they are only 69. A better way is to do this: current date - 70 years >= date_of_birth.

Andy

5. Registered User
Join Date
Jan 2009
Posts
46
Originally Posted by ARWinner
Using YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) is not accurate. If a person is born on 12/31/1939 and the current date is 08/31/2009, then your expression will say that that person is 70 when in fact they are only 69. A better way is to do this: current date - 70 years >= date_of_birth.

Andy
Hi Andy,

You are correct which is why I want my query to only include people who are 70 or older as of 7/01/2009. It looks like your query works. The last birthdate in my return results is 08/27/1939.

I also tried:

and CHAR(A.MBR_BIRTH_DT, USA) <= '07-01-1939'

This did not work. It returned people who had birthdates after 1939. I am puzzled why it failed. Any idea why? Thanks for all the help everyone!

6. Registered User
Join Date
Nov 2005
Location
IL
Posts
557
Originally Posted by ARWinner
Using YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) is not accurate. If a person is born on 12/31/1939 and the current date is 08/31/2009, then your expression will say that that person is 70 when in fact they are only 69. A better way is to do this: current date - 70 years >= date_of_birth.

Andy
That is true Andy, but can you solve a sudoki puzzle using SQL

BTW. Remove and A.MBR_BIRTH_DT <> '01/01/0001' from the SQL. It is totally redundant check.

7. Registered User
Join Date
Nov 2005
Location
IL
Posts
557
Originally Posted by rockdave35
Hi Andy,

You are correct which is why I want my query to only include people who are 70 or older as of 7/01/2009. It looks like your query works. The last birthdate in my return results is 08/27/1939.

I also tried:

and CHAR(A.MBR_BIRTH_DT, USA) <= '07-01-1939'

This did not work. It returned people who had birthdates after 1939. I am puzzled why it failed. Any idea why? Thanks for all the help everyone!
Maybe because your format is different

db2 "select CHAR(current date, USA) from sysibm.SYSDUMMY1"

1
----------
08/31/2009

1 record(s) selected.

8. Registered User
Join Date
Jan 2003
Posts
4,310
Originally Posted by rockdave35

I also tried:

and CHAR(A.MBR_BIRTH_DT, USA) <= '07-01-1939'

This did not work. It returned people who had birthdates after 1939. I am puzzled why it failed. Any idea why? Thanks for all the help everyone!

This is because you are now comparing strings and not dates. '06-30-2003' is less than '07-01-1939'.

Andy

9. Registered User
Join Date
May 2009
Posts
509
Try this:
Code:
```SELECT A.MBR_SSN_NBR AS SSN,
A.MBR_F_NM AS FIRST_NAME,
A.MBR_L_NM AS LAST_NAME,
CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,
YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
YEAR(CURRENT DATE - A.MBR_BIRTH_DT) as AGE

FROM DSNP.PR01_T_MBR A,
DSNP.PR01_T_MBR_SYS B
where A.MBR_SSN_NBR = B.MBR_SSN_NBR
and YEAR(CURRENT DATE - A.MBR_BIRTH_DT) >= 70
and B.mbr_stat_cd = '1'```
When you subtract a date from a date you get a date duration. this is an interger that contains the number of year, months and days the dates are different. For example:
CURRENT DATE - DATE('1920-10-11') returns 881020 which is 88 years 10 months and 20 days. You can use the YEAR function to extract just the year since that is all you need.

PS You can, of course, use any starting date instead of CURRENT DATE that you like or need.

10. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Why not use "and A.MBR_BIRTH_DT < DATE('07/01/1939') - 70 YEARS"?

11. Registered User
Join Date
May 2009
Posts
509
tonkuma, there is nothing wrong with your example (just as there is probably nothing wrong with most of the other suggestions) in the WHERE CLAUSE. But the request also returns the Age in the Select and you can't use that (or most of the others) in the Select and ensure a correct answer. This may not be true when the Where clause if factored in but subtracting the Year of one date from the year of another date is not an accurate way to get someone's age. If you birth date was Jun 30, that would indicate you are the same age on Jan 1 as you would be on Dec 31.

By using the Date - Date, the date duration handles all of that for you. Since I use it in the Select, I didn't see any reason to use different logic in the Where clause.

But as with most SQL, there are many different ways to get a result.

12. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by ARWinner
Using YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) is not accurate. If a person is born on 12/31/1939 and the current date is 08/31/2009, then your expression will say that that person is 70 when in fact they are only 69. A better way is to do this: current date - 70 years >= date_of_birth.

Andy
In this case YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) AS AGE on SELECT clause is not good, too.

But anyway, thanks

13. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
You can see a difference between:

year(current date) - year(date('1962-10-12'))
and
year(current date - date('1962-10-12'))
....

Just run the query:

select
year(current date) - year(date('1962-10-12')) wrong,
year(current date - date('1962-10-12')) righ
from sysibm.sysdummy1
So, just make the right change and you'll the right query:

SELECT A.MBR_SSN_NBR AS SSN,
A.MBR_F_NM AS FIRST_NAME,
A.MBR_L_NM AS LAST_NAME,
CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,
YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
YEAR(CURRENT DATE - date(A.MBR_BIRTH_DT)) as AGE

FROM DSNP.PR01_T_MBR A,
DSNP.PR01_T_MBR_SYS B
where A.MBR_SSN_NBR = B.MBR_SSN_NBR
YEAR(CURRENT DATE - date(A.MBR_BIRTH_DT)) >= 70
and A.MBR_BIRTH_DT > date('0001-01-01')
and B.mbr_stat_cd = '1'
Lenny

14. Registered User
Join Date
Jan 2009
Posts
46
Originally Posted by Lenny77
In this case YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) AS AGE on SELECT clause is not good, too.

But anyway, thanks
Ok, I'm confused again. How can I get their actual age in the Select statement?

15. Registered User
Join Date
Jan 2009
Posts
46
Originally Posted by Stealth_DBA
Try this:
Code:
```SELECT A.MBR_SSN_NBR AS SSN,
A.MBR_F_NM AS FIRST_NAME,
A.MBR_L_NM AS LAST_NAME,
CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,
YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
YEAR(CURRENT DATE - A.MBR_BIRTH_DT) as AGE

FROM DSNP.PR01_T_MBR A,
DSNP.PR01_T_MBR_SYS B
where A.MBR_SSN_NBR = B.MBR_SSN_NBR
and YEAR(CURRENT DATE - A.MBR_BIRTH_DT) >= 70
and B.mbr_stat_cd = '1'```
When you subtract a date from a date you get a date duration. this is an interger that contains the number of year, months and days the dates are different. For example:
CURRENT DATE - DATE('1920-10-11') returns 881020 which is 88 years 10 months and 20 days. You can use the YEAR function to extract just the year since that is all you need.

PS You can, of course, use any starting date instead of CURRENT DATE that you like or need.

Thank you Stealth for your explanation. Just to clarify, in DB2, I can not subtract two string values. However, the date functions converts to integer values which can then be subtracted. Is this correct?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•