如何在 Oracle 上使用 SQL 获得当前年份？

我需要在 SQL 语句中添加当前年份作为变量，如何使用 SQL 检索当前年份？

也就是说。

BETWEEN
TO_DATE('01/01/**currentYear** 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
AND
TO_DATE('31/12/**currentYear** 23:59:59', 'DD/MM/YYYY HH24:MI:SS')

465557 次浏览

小开

最佳答案

Using to_char:

select to_char(sysdate, 'YYYY') from dual;

In your example you can use something like:

BETWEEN trunc(sysdate, 'YEAR')
AND add_months(trunc(sysdate, 'YEAR'), 12)-1/24/60/60;

The comparison values are exactly what you request:

select trunc(sysdate, 'YEAR') begin_year
, add_months(trunc(sysdate, 'YEAR'), 12)-1/24/60/60 last_second_year
from dual;


BEGIN_YEAR  LAST_SECOND_YEAR
----------- ----------------
01/01/2009  31/12/2009

小开

Another option is:

SELECT *
FROM TABLE
WHERE EXTRACT( YEAR FROM date_field) = EXTRACT(YEAR FROM sysdate)

小开

Use extract(datetime) function it's so easy, simple.

It returns year, month, day, minute, second

Example:

select extract(year from sysdate) from dual;

小开

To display the current system date in oracle-sql

   select sysdate from dual;

小开

Yet another option would be:

SELECT * FROM mytable
WHERE TRUNC(mydate, 'YEAR') = TRUNC(SYSDATE, 'YEAR');

小开

Since we are doing this one to death - you don't have to specify a year:

select * from demo
where  somedate between to_date('01/01 00:00:00', 'DD/MM HH24:MI:SS')
and     to_date('31/12 23:59:59', 'DD/MM HH24:MI:SS');

However the accepted answer by FerranB makes more sense if you want to specify all date values that fall within the current year.

小开

Why not use YEAR function?

SELECT * FROM table WHERE YEAR(date_field)=YEAR(SYSDATE);