Oracle查询以计算当前年龄 [英] Oracle query to calculate current age

问题描述

我想从Oracle表的DOB(出生日期)字段中计算当前的年龄.

DOB字段的数据类型为varchar，其日期以"DD-MON-YY"格式存储.

当我从10-JAN-49之类的日期开始计算一个人的当前年龄时，查询将返回负数.另外，我观察到，如果日期为13到49年，则结果为负.

示例

22-NOV-83 -valid result
09-FEB-58 --valid result
05-JUN-49 - Invalid result like -36

执行查询以供参考

select round(MONTHS_BETWEEN(sysdate,to_date(dob,'DD-MON-RR'))/12)||' Yrs' 
from birth

感谢您的帮助！

解决方案

要解决21世纪的问题，只需稍微修改@the_silk的答案即可:

SELECT
  CASE WHEN SUBSTR(dob, -2, 2) > 13
  THEN FLOOR
        (
            MONTHS_BETWEEN
            (
                SYSDATE
            ,   TO_DATE(SUBSTR(dob, 1, 7) || '19' || SUBSTR(dob, -2, 2), 'DD-MON-YYYY')
            ) / 12
        )
  ELSE
       FLOOR(MONTHS_BETWEEN(sysdate,TO_DATE(dob,'DD-MON-YY'))/12)
  END
FROM
birth

请注意，尽管这假定'00'和'13'之间的任何日期年份为21世纪，所以仅当您要构建一个一次性脚本时才应使用此sql，否则它将过期并且不久就会失效.

最好的解决方案是重建该表，将varchar列转换为date列，如Ben所暗示的那样.

I want to calculate current age of person from DOB(date of birth) field in Oracle table.

Data type of DOB field is varchar and the is date stored in format 'DD-MON-YY'.

when I calculate current age of a person from date like 10-JAN-49 the query will return age in negative. Also, I observed that if date has year 13 to 49 it gives negative result.

Examples

22-NOV-83 -valid result
09-FEB-58 --valid result
05-JUN-49 - Invalid result like -36

Query Executed for reference

select round(MONTHS_BETWEEN(sysdate,to_date(dob,'DD-MON-RR'))/12)||' Yrs' 
from birth

Any help is appreciated!

解决方案

To get round the 21st century problem, just modifying @the_silk's answer slightly:

SELECT
  CASE WHEN SUBSTR(dob, -2, 2) > 13
  THEN FLOOR
        (
            MONTHS_BETWEEN
            (
                SYSDATE
            ,   TO_DATE(SUBSTR(dob, 1, 7) || '19' || SUBSTR(dob, -2, 2), 'DD-MON-YYYY')
            ) / 12
        )
  ELSE
       FLOOR(MONTHS_BETWEEN(sysdate,TO_DATE(dob,'DD-MON-YY'))/12)
  END
FROM
birth

Please be aware though that this assumes that any date year between '00' and '13' is 21st century, so this sql should only be used if you are building a one off throwaway script, otherwise it will become out of date and invalid before long.

The best solution would be to rebuild this table, converting the varchar column into a date column, as alluded to by Ben.

这篇关于Oracle查询以计算当前年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！