| View previous topic :: View next topic |
| Author |
Message |
omswarup
Beginner
Joined: 12 Dec 2006
Posts: 6
Topics: 2
Location: Bangalore
|
 Posted: Mon Jun 14, 2010 10:50 pm Post subject: Calculate the difference between 2 dates in years |
 |
|
I have a situation in which i have a column in a Db2 tabel which gives me Date of Birth, is there anyways of writing a query which would get me the details of the person whose age is greater than 18 years as of the date on which the query is being run.
There is no other field i can check on apart form the date of birth field on it.
Instead of writing a program i thought if there is a way of writing a query which can get this detail, it would be faster.
The date format is in CCYY-MM-DD format
Please help |
|
| Back to top |
|
 |
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 15, 2010 10:16 am Post subject: |
|
|
omswarup,
As papadi pointed you can use date arithmetic to get validate the age. The result of date arithmetic is DECIMAL(8,0) which number of years, months, and days between the two dates. Since you are interested only in the year portion divide the result by 10,000 and only take the integer portion and check if it is greater than 18 like shown below
| Code: |
SELECT *
FROM TABLE
WHERE INT(CURRENT DATE - Your db2 DATE col) / 10000 >= 18
;
|
Kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Tue Jun 15, 2010 10:42 am Post subject: |
|
|
| kolusu wrote: | omswarup,
As papadi pointed you can use date arithmetic to get validate the age. The result of date arithmetic is DECIMAL(8,0) which number of years, months, and days between the two dates. Since you are interested only in the year portion divide the result by 10,000 and only take the integer portion and check if it is greater than 18 like shown below
| Code: |
SELECT *
FROM TABLE
WHERE INT(CURRENT DATE - Your db2 DATE col) / 10000 >= 18
;
|
Kolusu |
This is the way I do it. Not sure if DB2 translates the code to the same as yours.
| Code: |
SELECT *
FROM your table
WHERE YEAR(CURRENT DATE - BIRTH_DATE ) >= 18
fetch first 10 rows only |
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 15, 2010 10:54 am Post subject: |
|
|
NASCAR9,
yep it works and actually is much cleaner than mine . Thanks for the tip
Kolusu |
|
| Back to top |
|
|
omswarup
Beginner
Joined: 12 Dec 2006
Posts: 6
Topics: 2
Location: Bangalore
|
| Posted: Wed Jun 16, 2010 4:12 am Post subject: |
|
|
Thanks a lot Kolusu and papadi for the inputs.
Regards,
Omswarup
| NASCAR9 wrote: | | kolusu wrote: | omswarup,
As papadi pointed you can use date arithmetic to get validate the age. The result of date arithmetic is DECIMAL(8,0) which number of years, months, and days between the two dates. Since you are interested only in the year portion divide the result by 10,000 and only take the integer portion and check if it is greater than 18 like shown below
| Code: |
SELECT *
FROM TABLE
WHERE INT(CURRENT DATE - Your db2 DATE col) / 10000 >= 18
;
|
Kolusu |
This is the way I do it. Not sure if DB2 translates the code to the same as yours.
| Code: |
SELECT *
FROM your table
WHERE YEAR(CURRENT DATE - BIRTH_DATE ) >= 18
fetch first 10 rows only |
|
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
