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 |
|
|
|
|