How to Get the Current Year Using SQL on Oracle

How do I get the current year using SQL on Oracle?

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

Oracle get current Year & Previous Year and coming year from select query

Don't have Oracle accessible to test but might try:

Select id,  extract(year from sysdate) as currentyear ,   extract(year from sysdate)-1 Previous_Year ,
extract(year from sysdate)+1 Upcoming Year from testtable

SQL Function to select first day of current year and everyday after

Are you looking for something like this:

SELECT
    ST_DT + LEVEL - 1,
    TO_CHAR(ST_DT + LEVEL - 1, 'IW') AS DY
FROM
    (
        SELECT
            TRUNC(SYSDATE, 'YEAR') ST_DT,
            TRUNC(SYSDATE, 'YEAR') + INTERVAL '12' MONTH - INTERVAL '1' DAY END_DT
        FROM DUAL
    )
CONNECT BY LEVEL <= END_DT - ST_DT
ORDER BY LEVEL

Cheers!!

Trying to get current year in Oracle Apex

DECLARE
CURRENT_YEAR VARCHAR2(5);
BEGIN
SELECT TO_CHAR(SYSDATE,'YYYY') INTO CURRENT_YEAR FROM DUAL;
RETURN CURRENT_YEAR;
END;

use the sql query and paste the item default in oracle apex page item, it will return the current year. not getting an error.
check the below image.

SQL query all data of last year & current year dynamically

You can reformat by using to_char(sysdate,'RRRR')-1 within to_date() conversion :

SELECT shipment_date, shipment_status, load_country_code, unload_country_code,
       count(shipment) as "count"
  FROM shipment
 WHERE shipment_date  >= to_date( concat('01-01-',to_char(sysdate,'RRRR')-1) , 'DD-MM-RR')
 GROUP BY shipment_date, shipment_status, load_country_code, unload_country_code

pay attention that I didn't compare only the year part but formatted as a date, since in this way we do not unveil the index on shipment_day column if exists any.

Or another option would be replacing

WHERE shipment_date >= to_date( concat('01-01-',to_char(sysdate,'RRRR')-1) , 'DD-MM-RR')
with

WHERE shipment_date >= trunc(add_months(sysdate,-12),'RRRR')

alternatively

Demo

current year's date of birth using oracle sql

This logic should work:

with tmp_dob as (
      select to_date('19900101', 'YYYYMMDD') as Birthday from dual union all
      select to_date('19901231', 'YYYYMMDD') Birthday from dual union all
      select to_date('20040229', 'YYYYMMDD') Birthday from dual union all
      select to_date('20041231', 'YYYYMMDD') Birthday from dual union all
      select to_date('20171231', 'YYYYMMDD') Birthday from dual union all
      select to_date('20051231', 'YYYYMMDD') Birthday from dual 
    )
select Birthday,
       add_months(birthday, 12 * (extract(year from sysdate) - extract(year from birthday)))
from tmp_dob;

SQL query to get current and last year sales

Since I want the query to return day by day sales I used MT0 answer and added the dates, this way I can get the data for all year days.

WITH AllYear AS
 (select to_date('2016-01-01', 'yyyy-mm-dd') + level - 1 AS dobs
    from dual
  connect by level <= 366)

SELECT dobs AS "DATE",
       Store,
       nvl(SUM(CASE
                 WHEN t.Date = dobs THEN
                  t.sales
               END),
           0) AS "This Year Sales",
       nvl(SUM(CASE
                 WHEN t.Date = dobs-365 THEN
                  t.sales
               END),
           0) AS "Last Year Sales"
  FROM Sales t,AllYear
 where dobs='01-Jan-2016'
 GROUP BY Store
 ORDER BY Store;

---

Related Topics