Date and Time Handling
Domain 12 tests Oracle datetime data types, arithmetic, conversion, and time zone behavior. You must know the difference between DATE and TIMESTAMP and how session settings affect results.
Core rules
- DATE stores year, month, day, hour, minute, and second. TIMESTAMP adds fractional seconds. TIMESTAMP WITH TIME ZONE stores a time zone offset; TIMESTAMP WITH LOCAL TIME ZONE normalizes to the database time zone for storage.
- SYSDATE returns the database server date and time. CURRENT_DATE returns the session date and time. SYSTIMESTAMP returns the database timestamp with time zone.
- Date arithmetic uses numbers as days. Adding 1 adds one day; adding 1/24 adds one hour. Date subtraction returns a number of days.
- ADD_MONTHS shifts by months, respecting month-end behavior. MONTHS_BETWEEN returns fractional months. LAST_DAY returns the last day of the month. NEXT_DAY returns the next named weekday after a date.
- EXTRACT retrieves a component such as YEAR, MONTH, DAY, or HOUR. INTERVAL types represent durations and can be added to datetime values.
Worked example
SELECT employee_id, hire_date + 30 AS review_date, ADD_MONTHS(hire_date,6) AS six_month, EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees WHERE hire_date >= DATE '2020-01-01';
hire_date + 30 adds 30 days. ADD_MONTHS adds six calendar months. EXTRACT returns the four-digit year. The WHERE uses a DATE literal, avoiding dependence on NLS_DATE_FORMAT for that literal.
Traps
Adding 1 adds a day, not a month or year. TIMESTAMP fractional seconds can be lost when converted to DATE. CURRENT_DATE depends on session time zone, while SYSDATE reflects the database server. Implicit conversion of strings to dates depends on NLS settings. MONTHS_BETWEEN can return a fraction, not an integer.
Key points
- DATE precision is to the second; TIMESTAMP adds fractions.
- Date arithmetic uses days as the unit.
- CURRENT_DATE follows session time zone.
- ADD_MONTHS handles calendar months.
- Use DATE literals to avoid NLS dependence.