Oracle Date Functions Cheat Sheet - TO_DATE, SYSDATE & Date Arithmetic

Oracle Date Functions Cheat Sheet

Quick reference for Oracle date and timestamp functions with examples.

Current Date/Time Functions

-- Current date and time
SELECT SYSDATE FROM DUAL;                    -- Database server date/time
SELECT CURRENT_DATE FROM DUAL;               -- Session timezone date
SELECT SYSTIMESTAMP FROM DUAL;               -- Database timestamp with timezone
SELECT CURRENT_TIMESTAMP FROM DUAL;          -- Session timestamp with timezone
SELECT LOCALTIMESTAMP FROM DUAL;             -- Session timestamp without timezone

-- Database timezone
SELECT DBTIMEZONE FROM DUAL;
SELECT SESSIONTIMEZONE FROM DUAL;

---

Date Format Models

Common Format Elements

Element	Description	Example
YYYY	4-digit year	2024
YY	2-digit year	24
MM	Month (01-12)	03
MON	Abbreviated month	MAR
MONTH	Full month name	MARCH
DD	Day of month (01-31)	15
DY	Abbreviated day	FRI
DAY	Full day name	FRIDAY
HH24	Hour (00-23)	14
HH	Hour (01-12)	02
MI	Minutes (00-59)	30
SS	Seconds (00-59)	45
AM/PM	Meridian indicator	PM
D	Day of week (1-7)	6
DDD	Day of year (1-366)	074
WW	Week of year (1-53)	11
Q	Quarter (1-4)	1

---

TO_DATE Function

Convert string to date

-- Basic conversion
SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') FROM DUAL;

-- With time
SELECT TO_DATE('2024-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Common formats
SELECT TO_DATE('15-MAR-2024', 'DD-MON-YYYY') FROM DUAL;
SELECT TO_DATE('03/15/2024', 'MM/DD/YYYY') FROM DUAL;
SELECT TO_DATE('March 15, 2024', 'MONTH DD, YYYY') FROM DUAL;

---

TO_CHAR Function

Convert date to string

-- Format date
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;          -- 2024-03-15
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;         -- 15-MAR-2024
SELECT TO_CHAR(SYSDATE, 'Day, Month DD, YYYY') FROM DUAL; -- Friday, March 15, 2024

-- With time
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS AM') FROM DUAL;

-- Day and week info
SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;     -- FRIDAY
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL;      -- FRI
SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL;       -- Day number
SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL;      -- Week number
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;       -- Quarter

---

Date Arithmetic

Adding/Subtracting Days

-- Add days
SELECT SYSDATE + 7 FROM DUAL;                 -- 7 days from now
SELECT SYSDATE - 30 FROM DUAL;                -- 30 days ago

-- Add hours/minutes/seconds
SELECT SYSDATE + 1/24 FROM DUAL;              -- 1 hour from now
SELECT SYSDATE + 30/1440 FROM DUAL;           -- 30 minutes from now
SELECT SYSDATE + 45/86400 FROM DUAL;          -- 45 seconds from now

ADD_MONTHS Function

-- Add months
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;      -- 1 month from now
SELECT ADD_MONTHS(SYSDATE, -3) FROM DUAL;     -- 3 months ago
SELECT ADD_MONTHS(SYSDATE, 12) FROM DUAL;     -- 1 year from now

MONTHS_BETWEEN Function

-- Calculate months between dates
SELECT MONTHS_BETWEEN(SYSDATE, DATE '2024-01-01') FROM DUAL;
SELECT MONTHS_BETWEEN(DATE '2024-12-31', DATE '2024-01-01') FROM DUAL;  -- 11.968...

Days Between Dates

-- Calculate days between
SELECT SYSDATE - DATE '2024-01-01' FROM DUAL;
SELECT DATE '2024-12-31' - DATE '2024-01-01' FROM DUAL;  -- 365

---

Date Truncation & Rounding

TRUNC Function

-- Truncate to start of period
SELECT TRUNC(SYSDATE) FROM DUAL;              -- Start of today (midnight)
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;        -- First of current month
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;      -- First of current year
SELECT TRUNC(SYSDATE, 'Q') FROM DUAL;         -- First of current quarter
SELECT TRUNC(SYSDATE, 'WW') FROM DUAL;        -- Start of current week
SELECT TRUNC(SYSDATE, 'HH24') FROM DUAL;      -- Start of current hour

ROUND Function

-- Round to nearest period
SELECT ROUND(SYSDATE) FROM DUAL;              -- Nearest day
SELECT ROUND(SYSDATE, 'MM') FROM DUAL;        -- Nearest month
SELECT ROUND(SYSDATE, 'YYYY') FROM DUAL;      -- Nearest year

---

First/Last Day Functions

-- Last day of month
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY(DATE '2024-02-01') FROM DUAL;  -- 2024-02-29 (leap year)

-- First day of month
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;

-- First day of year
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;

---

NEXT_DAY Function

-- Next occurrence of day
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL;        -- 1=Sunday, 2=Monday, etc.

---

EXTRACT Function

-- Extract date components
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;

-- From timestamp
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM DUAL;
SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM DUAL;
SELECT EXTRACT(SECOND FROM SYSTIMESTAMP) FROM DUAL;

---

Timestamp Functions

NUMTODSINTERVAL & NUMTOYMINTERVAL

-- Add intervals
SELECT SYSDATE + NUMTODSINTERVAL(5, 'HOUR') FROM DUAL;
SELECT SYSDATE + NUMTODSINTERVAL(30, 'MINUTE') FROM DUAL;
SELECT SYSDATE + NUMTOYMINTERVAL(6, 'MONTH') FROM DUAL;

Timestamp Arithmetic

-- Timestamp differences
SELECT SYSTIMESTAMP - TIMESTAMP '2024-01-01 00:00:00' FROM DUAL;

-- Convert interval to days
SELECT EXTRACT(DAY FROM (SYSTIMESTAMP - TIMESTAMP '2024-01-01 00:00:00')) FROM DUAL;

Timestamp Conversion

-- Cast between types
SELECT CAST(SYSDATE AS TIMESTAMP) FROM DUAL;
SELECT CAST(SYSTIMESTAMP AS DATE) FROM DUAL;

-- With timezone
SELECT FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'America/New_York') FROM DUAL;
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL;

---

Common Date Queries

Start/End of Periods

-- Today
SELECT TRUNC(SYSDATE) AS today_start,
       TRUNC(SYSDATE) + 1 - 1/86400 AS today_end
FROM DUAL;

-- This week (Sunday to Saturday)
SELECT TRUNC(SYSDATE, 'WW') AS week_start,
       TRUNC(SYSDATE, 'WW') + 7 - 1/86400 AS week_end
FROM DUAL;

-- This month
SELECT TRUNC(SYSDATE, 'MM') AS month_start,
       LAST_DAY(SYSDATE) + 1 - 1/86400 AS month_end
FROM DUAL;

-- This quarter
SELECT TRUNC(SYSDATE, 'Q') AS quarter_start,
       ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 3) - 1/86400 AS quarter_end
FROM DUAL;

-- This year
SELECT TRUNC(SYSDATE, 'YYYY') AS year_start,
       ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - 1/86400 AS year_end
FROM DUAL;

Business Day Calculations

-- Is it a weekday?
SELECT CASE WHEN TO_CHAR(SYSDATE, 'DY') NOT IN ('SAT', 'SUN')
            THEN 'Weekday' ELSE 'Weekend' END
FROM DUAL;

-- Next business day
SELECT CASE TO_CHAR(SYSDATE, 'DY')
         WHEN 'FRI' THEN SYSDATE + 3
         WHEN 'SAT' THEN SYSDATE + 2
         ELSE SYSDATE + 1
       END AS next_business_day
FROM DUAL;

Age Calculation

-- Years between dates
SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, DATE '1990-05-15') / 12) AS age
FROM DUAL;

-- Detailed age
SELECT
  FLOOR(MONTHS_BETWEEN(SYSDATE, DATE '1990-05-15') / 12) AS years,
  MOD(FLOOR(MONTHS_BETWEEN(SYSDATE, DATE '1990-05-15')), 12) AS months
FROM DUAL;

---

Date Format Error Prevention

-- Safe date parsing
SELECT TO_DATE('2024-13-01', 'YYYY-MM-DD' DEFAULT NULL ON CONVERSION ERROR) FROM DUAL;

-- Validate date format
SELECT CASE
         WHEN REGEXP_LIKE(date_col, '^\d{4}-\d{2}-\d{2}$')
         THEN TO_DATE(date_col, 'YYYY-MM-DD')
       END
FROM your_table;

---

Related Errors

• ORA-01830: Date Format Picture Ends
• ORA-01843: Not a Valid Month
• ORA-01861: Literal Does Not Match Format

---

Related Resources

• PL/SQL Exception Handling
• Common SQL Issues
• V$SQLTEXT Guide