Oracle查询在使用DOB计算当前年龄时返回负结果 [英] Oracle query returns negative result while calculating current age using DOB
问题描述
我想在Oracle表中从dob字段计算当前人的年龄。
DOB字段的数据类型是varchar,日期以'DD-MON格式存储-YY'。
当我计算从10-JAN-49开始的当前年龄,然后查询结果年龄为负。
和我观察到,如果日期为13到49年,则会给出负面结果。
例子
22- NOV-83-结果有效
09-FEB-58 - 结果无效
05-JUN-49 - 无效结果如-36
查询执行参考
选择回合(MONTHS_BETWEEN(sysdate,to_date(dob,'DD-MON-RR'))/ 12 )||'Yrs'从出生
请帮助我。
I want to calculate current age of person from dob field in Oracle table.
Data type of DOB field is varchar and date stored in format 'DD-MON-YY'.
when I calculate current age of from date like 10-JAN-49 then query result age in negative.
and 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
Please help me.
推荐答案
这是由你的缺陷引起的设计!您多久可以阅读CP和其他编程网站上的日期 绝不能存储为char / varchar / nvarchar?
看起来像05-JUN-49
被解释为05-JUN-2049
,因此正确计算了-36。 />
改变你的设计!这是唯一正确的解决方案!
That's caused by your defective design! How often can you read on CP and other programming sites that a date MUST NOT be stored as a char/varchar/nvarchar?
Looks like05-JUN-49
gets interpreted as05-JUN-2049
, and consequently -36 is correctly calculated.
Change your design! That is the only correct solution!
这篇关于Oracle查询在使用DOB计算当前年龄时返回负结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!