Skip to Main Content
  • Questions
  • Need to calculate Age as part of select

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Denise.

Asked: September 16, 2020 - 1:09 pm UTC

Answered by: Chris Saxon - Last updated: October 05, 2020 - 12:32 pm UTC

Category: PL/SQL - Version: 12CR2

Viewed 100+ times

You Asked

Hi,
We just went live on Oracle a couple of weeks ago. I have a legacy process that includes running a script that was coded for Sybase. I have most of it converted to Oracle, but I'm having trouble with the Age field (it's the last piece I need to get working). I thought about just including the Age piece... then thought to include the entire script for context if nothing else.
Thanks in advance for the assist!
-Denise

Current legacy code
SELECT DISTINCT 
   meme.MEME_MEDCD_NO,
   meme.MEME_BIRTH_DT,
   AGE =
      CASE WHEN 
         (
         month(convert(datetime, meme.MEME_BIRTH_DT, 103))*100)+
           day(convert(datetime, meme.MEME_BIRTH_DT, 103)) - 
              ((month(getdate())*100)+day(getdate())) <= 0 THEN 
              DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())</b>
      ELSE 
         DATEDIFF(YEAR,convert(datetime, meme.MEME_BIRTH_DT, 103),getdate())-1
      END,
   sbsb.SBSB_ID, 
   mepe.MEPE_EFF_DT, 
   mepe.MEPE_TERM_DT, 
   mepe.MEPE_ELIG_IND, 
   mepe.CSPI_ID,
   sbad.SBAD_COUNTY AS 'Member_County',
   pdpd.LOBD_ID
FROM 
   dbo.CMC_MEME_MEMBER meme
   INNER JOIN dbo.CMC_MEPE_PRCS_ELIG mepe ON
      mepe.MEME_CK =meme.MEME_CK
   INNER JOIN dbo.CMC_SBSB_SUBSC     sbsb ON
      sbsb.SBSB_CK = meme.SBSB_CK
   INNER JOIN CMC_PDPD_PRODUCT       pdpd ON 
      mepe.PDPD_ID = pdpd.PDPD_ID
   INNER JOIN CMC_SBAD_ADDR          sbad ON
      sbsb.SBSB_CK = sbad.SBSB_CK AND
      sbsb.SBAD_TYPE_MAIL = sbad.SBAD_TYPE
WHERE 
   mepe.GRGR_CK IN (1,3,8)       AND
   mepe.MEPE_ELIG_IND = 'Y'      AND
   mepe.MEPE_EFF_DT  <= '09/01/2020' AND  -- Match file date
   mepe.MEPE_TERM_DT >= '09/01/2020' AND  -- Match file date
   meme.MEME_MEDCD_NO IN   
   (   
   )


and we said...

Connor recently made a post about this topic :)

https://connor-mcdonald.com/2020/09/11/age-calculations-just-how-old-are-you/

The key point to note is there are edge cases everywhere when it comes to date calculation.

One way to overcome these is to

- Convert the current and birth dates to numbers in YYYYMMDD form
- Subtract the birth value from the current date value
- Divide by 10,000
- Take the floor/trunc of this

e.g.:

trunc (
 (to_number ( to_char ( sysdate, 'YYYYMMDD' ) ) -
  to_number ( to_char ( birth_dt, 'YYYYMMDD' ) )
 ) / 10000
) calculated_age

and you rated our response

  (2 ratings)

Reviews

Can we use months_between?

October 04, 2020 - 1:42 pm UTC

Reviewer: vc from UK

May be a simple way is to use months_between function and divide it by 12.
Connor McDonald

Followup  

October 05, 2020 - 12:00 am UTC

There are a couple of boundary cases for which that does not work - see the blog post

Depends...

October 05, 2020 - 9:27 am UTC

Reviewer: Stew Ashton from France

Your age has legal ramifications: when you become an "adult", when you are of retirement age, etc. In some jurisdictions, those born on February 29th legally get older on February 28th. In those places, ADD_MONTHS is the way to go.

Best regards,
Stew
Chris Saxon

Followup  

October 05, 2020 - 12:32 pm UTC

Good point

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.