How do I get the current year using SQL on Oracle?
SqlOracleDateSql Problem Overview
I need to add the current year as a variable in an SQL statement, how can I retrieve the current year using SQL?
i.e.
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')
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
Another option is:
SELECT *
FROM TABLE
WHERE EXTRACT( YEAR FROM date_field) = EXTRACT(YEAR FROM sysdate)
Solution 3 - Sql
Use extract(datetime) function it's so easy, simple.
It returns year, month, day, minute, second
Example:
select extract(year from sysdate) from dual;
Solution 4 - Sql
Yet another option would be:
SELECT * FROM mytable
WHERE TRUNC(mydate, 'YEAR') = TRUNC(SYSDATE, 'YEAR');
Solution 5 - Sql
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.
Solution 6 - Sql
Why not use YEAR function?
SELECT * FROM table WHERE YEAR(date_field)=YEAR(SYSDATE);
Solution 7 - Sql
To display the current system date in oracle-sql
select sysdate from dual;