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')
To display the current system date in oracle-sql
select sysdate from dual;
Use extract(datetime)
function it's so easy, simple.
It returns year, month, day, minute, second
Example:
select extract(year from sysdate) from dual;
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.
Yet another option would be:
SELECT * FROM mytable
WHERE TRUNC(mydate, 'YEAR') = TRUNC(SYSDATE, 'YEAR');
Another option is:
SELECT *
FROM TABLE
WHERE EXTRACT( YEAR FROM date_field) = EXTRACT(YEAR FROM sysdate)
Source: Stackoverflow.com