函数EXTRACT()没有返回正确的结果 [英] Function EXTRACT() does not return the correct result

问题描述

我正在尝试使用函数EXTRACT来获得使用此查询的年数用户:

I'm trying to get the years users with this query using the function EXTRACT:

SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM contact.BIRTHDATE) AS CONTACT_YEARS 
FROM CONTACT contact

但是对于某些日期，例如 :contact.BIRTHDATE = '27 -AGO-40'函数EXTRACT(YEAR FROM contact.BIRTHDATE)返回2040，查询不起作用.

but for some dates, for example: contact.BIRTHDATE = '27-AGO-40' the function EXTRACT(YEAR FROM contact.BIRTHDATE) return 2040 and query does not work.

我也尝试使用to_date(contact.BIRTHDATE, 'dd/mm/yyyy') 但日期格式不会改变.

I also tried using to_date(contact.BIRTHDATE, 'dd/mm/yyyy') but the date format does not change.

谢谢！

推荐答案

您只用两位数字显示日期的年部分.如果显示全年，则可能会看到日期的世纪不正确.

You are only displaying the year component of the date with two-digits. If you display the full year then you will likely see that the dates have the incorrect century.

此外，与其减去年份来获得年差，不如使用以下方法更好:

Also, rather than subtracting the years to get the difference in years, you would be better using:

TRUNC( MONTHS_BETWEEN( sysdate, birthdate ) / 12 )

Oracle设置:

CREATE TABLE contact( BIRTHDATE DATE );

INSERT INTO contact ( birthdate )
SELECT DATE '1977-03-29' FROM DUAL UNION ALL
SELECT DATE '77-03-29'   FROM DUAL UNION ALL
SELECT DATE '1977-03-30' FROM DUAL;

查询:

SELECT TO_CHAR( birthdate, 'YYYY-MM-DD' ) AS full_year,
       TO_CHAR( birthdate, 'YY-MM-DD' )   AS short_year,
       EXTRACT( YEAR FROM sysdate ) - EXTRACT( YEAR FROM birthdate ) AS age,
       TRUNC( MONTHS_BETWEEN( sysdate, birthdate ) / 12 ) AS actual_age
FROM   contact;

输出:

FULL_YEAR  SHORT_YEAR AGE  ACTUAL_AGE
---------- ---------- ---- ----------
1977-03-29 77-03-29     40         40
0077-03-29 77-03-29   1940       1940
1977-03-30 77-03-30     40         39

这篇关于函数EXTRACT()没有返回正确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！