Mastering Oracle SYSDATE and Date Arithmetic

Performing Date Arithmetic with SYSDATE

Oracle's SYSDATE function returns the current date and time. You can add or subtract time intervals directly using arithmetic on date values, or use dedicated functions like ADD_MONTHS.

Adding Time Intervals

-- Add 1 year
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 12) FROM DUAL;

-- Add 1 month
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 1) FROM DUAL;

-- Add 1 week (7 days)
SELECT SYSDATE, TO_CHAR(SYSDATE + 7, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Add 1 day
SELECT SYSDATE, TO_CHAR(SYSDATE + 1, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Add 1 hour
SELECT SYSDATE, TO_CHAR(SYSDATE + 1/24, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Add 1 minute
SELECT SYSDATE, TO_CHAR(SYSDATE + 1/24/60, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Add 1 second
SELECT SYSDATE, TO_CHAR(SYSDATE + 1/24/60/60, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

Subtracting Time Intervals

-- Subtract 1 year
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -12) FROM DUAL;

-- Subtract 1 month
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -1) FROM DUAL;

-- Subtract 1 week
SELECT SYSDATE, TO_CHAR(SYSDATE - 7, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Subtract 1 day
SELECT SYSDATE, TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Subtract 1 hour
SELECT SYSDATE, TO_CHAR(SYSDATE - 1/24, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Subtract 1 minute
SELECT SYSDATE, TO_CHAR(SYSDATE - 1/24/60, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Subtract 1 second
SELECT SYSDATE, TO_CHAR(SYSDATE - 1/24/60/60, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

Extracting Date Parts Using TO_CHAR

The TO_CHAR function lets you format date values and extract components like year, month, day, etc.

-- Current date and week within month (W)
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD W HH24:MI:SS') FROM DUAL;

-- Day of week (1=Sunday, 2=Monday, ...)
SELECT SYSDATE, TO_CHAR(SYSDATE, 'D') FROM DUAL;

-- Year
SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL;

-- Quarter (Q)
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;

-- Month number (MM)
SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL;

-- Day of month (DD)
SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL;

-- Day of year (DDD)
SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL;

-- Week of year (WW)
SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL;

-- Week of month (W)
SELECT TO_CHAR(SYSDATE, 'W') FROM DUAL;

-- Day name (DAY)
SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;

-- Hour in 12-hour format (HH) or 24-hour format (HH24)
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;

Common Date Queries

Get Midnight of Current Day

SELECT TRUNC(SYSDATE) FROM DUAL;

Get Last Second of Current Day

SELECT TRUNC(SYSDATE) + 0.99999 FROM DUAL;

Get Midnight of Tomorrow

SELECT TRUNC(SYSDATE + 1) FROM DUAL;
-- or
SELECT TRUNC(SYSDATE) + 1 FROM DUAL;

Get First Day of Current Month

SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;

Get First Day of Next Month

SELECT TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') FROM DUAL;

Get Last Day of Current Month

SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT TRUNC(LAST_DAY(SYSDATE)) FROM DUAL;
SELECT TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') - 1 FROM DUAL;

Generate All Days of the Current Year

SELECT TRUNC(SYSDATE, 'YYYY') + LEVEL - 1 AS day_of_year
FROM DUAL
CONNECT BY LEVEL <= 366;

Day Number Within Current Year

SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL;

Quarter of a Date

SELECT CEIL(TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) / 3) FROM DUAL;
-- or
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;

Date Difference Calculations

When you subtract two Oracle DATE values, the result is in days. You can multip by 24, 60, etc., to get hours, minutes, seconds, or milliseconds.

Difference in Days

SELECT TO_DATE('2008-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
     - TO_DATE('2008-04-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS') AS days_diff
FROM DUAL;

Difference in Hours

SELECT (TO_DATE('2008-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
      - TO_DATE('2008-04-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) * 24 AS hours_diff
FROM DUAL;

Difference in Minutes

SELECT (TO_DATE('2008-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
      - TO_DATE('2008-04-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 AS minutes_diff
FROM DUAL;

Differecne in Seconds

SELECT (TO_DATE('2008-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
      - TO_DATE('2008-04-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 AS seconds_diff
FROM DUAL;

Difference in Milliseconds

SELECT (TO_DATE('2008-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
      - TO_DATE('2008-04-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000 AS millis_diff
FROM DUAL;

Difference in Months

Use MONTHS_BETWEEN for a precise month difference:

SELECT MONTHS_BETWEEN(TO_DATE('2008-05-20', 'YYYY-MM-DD'),
                      TO_DATE('2008-04-30', 'YYYY-MM-DD')) AS months_diff
FROM DUAL;

Diffference in Years

SELECT EXTRACT(YEAR FROM TO_DATE('2009-05-01', 'YYYY-MM-DD'))
     - EXTRACT(YEAR FROM TO_DATE('2008-04-30', 'YYYY-MM-DD')) AS years_diff
FROM DUAL;

Using Date Indexes Effectively

When querying on a DATE column with an index, it's best to avoid wrapping the column in functions. Instead, use date literals or set the session format:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Check for Leap Year

-- Check if current year is leap
SELECT DECODE(TO_CHAR(LAST_DAY(TRUNC(SYSDATE, 'YEAR') + 31), 'DD'), '29', 'Leap', 'Common') AS leap_year
FROM DUAL;

-- Check if two years from now is leap
SELECT DECODE(TO_CHAR(LAST_DAY(TRUNC(ADD_MONTHS(SYSDATE, 24), 'YEAR') + 31), 'DD'), '29', 'Leap', 'Common') AS leap_in_two_years
FROM DUAL;

Tags: Oracle SYSDATE SQL date functions date arithmetic TO_CHAR date format

Posted on Tue, 30 Jun 2026 17:03:48 +0000 by Lateuk

Freaks City