Oracle DATE Functions and Format Models

Oracle AI Database 26ai supplies strongly typed datetime values and a large family of SQL functions for displaying, comparing, and calculating with them. The established "DATE" datatype stores a calendar date and a time of day through whole seconds. Its fields are year, month, day, hour, minute, and second.

It is equally important to distinguish a stored value from its character representation. Oracle has no single display format that every session must use. A session's NLS_DATE_FORMAT setting affects implicit conversion of DATE values to and from text. The underlying value remains the same even when two clients display it differently.

Treat conversion as an explicit boundary. TO_DATE interprets character data and returns a DATE. TO_CHAR takes a datetime value and returns VARCHAR2 for display. A format model tells Oracle how to interpret the input text or construct the output text; it does not modify the internal datetime representation.

SELECT TO_CHAR(
         TO_DATE('2025-09-09 20:29:06', 'YYYY-MM-DD HH24:MI:SS'),
         'YYYY-MM-DD HH24:MI:SS'
       ) AS formatted_sale_time
FROM   dual;

The first model matches the incoming string and produces a DATE. The second creates the displayed value 2025-09-09 20:29:06. Because both models are explicit, the statement does not depend on the session's default date format.

When a constant requires a date but no time, an ANSI date literal is simpler: DATE '2023-03-15'. Its required Gregorian form is YYYY-MM-DD, and its time is midnight. Use TO_DATE with a matching model when the source really is character data or when a time component must be parsed.

Oracle still supports the RR format element for compatibility with two-digit year input. It applies century-selection rules based on the supplied year and the current year. It is not simply a “rounding” format. Four-digit input and YYYY are clearer for new systems and remove the ambiguity that two-digit years introduce.

The following query applies four models to one known value. The common table expression converts the input once so every output column describes the same instant represented as an Oracle DATE:

WITH sample_value AS (
  SELECT TO_DATE(
           '2025-09-09 20:29:06',
           'YYYY-MM-DD HH24:MI:SS'
         ) AS sample_date
  FROM   dual
)
SELECT TO_CHAR(sample_date, 'MM/DD/YYYY  HH:MI A.M.') AS sample_one,
       TO_CHAR(sample_date, 'DDD - YYYY HH:MI:SS') AS sample_two,
       TO_CHAR(sample_date, 'FMDay", "Month FMDD", ''"YY", "SSSSS') AS sample_three,
       TO_CHAR(sample_date, 'HH:MI Mon. DD, Year') AS sample_four
FROM   sample_value;

The capitalization of alphabetic output follows the capitalization used in the model. The FM modifier in SAMPLE_THREE suppresses blank padding around names while a second FM restores normal filling before DD. Quoted punctuation and text are copied into the result. The value 73746 is the number of seconds from midnight through 8:29:06 p.m.

Oracle interprets a number added to or subtracted from a DATE as a number of days. Whole numbers adjust whole days. A fraction represents part of a day, so 1 / 24 is one hour. Intervals can make the time unit more explicit when working with timestamp values or complex time calculations.

SELECT sales_date,
       sales_date + 2  AS plus_two,
       sales_date - 10 AS minus_ten
FROM   customer_sale;

The last row uses February 28 because 2025 is not a leap year. Changing a year in sample data can therefore require changing the day and recalculating every derived result. A blind replacement of two-digit years with four-digit years is not sufficient.

A predicate must account for the stored time component even when a report displays only a date. Equality with DATE '2025-03-01' matches values at exactly midnight on March 1; it does not match rows later that morning or evening. A half-open range expresses the complete calendar day without discarding the stored time:

SELECT sales_date
FROM   customer_sale
WHERE  sales_date >= DATE '2025-03-01'
AND    sales_date <  DATE '2025-03-02';

The lower boundary is inclusive and the next day's midnight is exclusive. Consequently, every representable DATE value on March 1 is included. This pattern also extends naturally to TIMESTAMP data because it does not depend on choosing a final time such as 23:59:59; timestamp values can contain fractional seconds after that point.

Avoid converting the column to text in the predicate merely to ignore its time. A condition such as TO_CHAR(sales_date, 'YYYY-MM-DD') = '2025-03-01' mixes comparison with presentation, applies a function to every candidate value, and requires an exact character format. Similarly, TRUNC(sales_date) = DATE '2025-03-01' expresses the intended day but may require a matching function-based index to use that transformed expression efficiently. A range on the original column can use an ordinary index while keeping the date boundaries explicit.

Do not substitute BETWEEN with an upper value of DATE '2025-03-02'. BETWEEN includes both endpoints and would therefore include exactly midnight at the start of March 2. Half-open ranges provide a consistent convention for adjacent days and prevent boundary rows from appearing in two daily groups.

Subtracting one DATE from another returns a NUMBER representing elapsed days. The result is positive when the first value is later, negative when it is earlier, and fractional when the stored times of day contribute part of a day.

SELECT sales_date,
       sales_date - DATE '2023-03-15' AS diff_days
FROM   customer_sale;

A display such as DD-MON-YYYY can hide hours, minutes, and seconds that remain stored in the column. Those hidden fields explain the fractional results. If a business rule deliberately compares calendar days at midnight, use TRUNC(sales_date) - DATE '2023-03-15'. Do not discard time automatically when the time is meaningful.

The earlier SQL*Plus version used TO_DATE('15-MAR-2023'). That expression can work when NLS_DATE_FORMAT and NLS_DATE_LANGUAGE recognize the input, but it relies on session configuration. If text conversion is required, write TO_DATE('15-MAR-2023', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=English'). For this midnight constant, the ANSI literal is simpler.

Oracle AI Database 26ai also documents DATEDIFF and its synonym TIMESTAMPDIFF. They return an integer count of boundaries crossed for a requested unit, including year, quarter, month, week, day, hour, minute, and second. Direct DATE subtraction instead returns elapsed days and can include a fraction.

SELECT DATEDIFF(
         DAY,
         DATE '2023-03-15',
         DATE '2025-03-01'
       ) AS day_boundaries
FROM   dual;

Choose the operation that matches the business question. “How many days elapsed?” and “How many calendar-day boundaries were crossed?” can produce different answers when times, time zones, or coarser units are involved.

SELECT sales_date,
       ADD_MONTHS(sales_date, 3) AS three_months,
       NEXT_DAY(sales_date, 'MONDAY') AS next_monday
FROM customer_sale;

ADD_MONTHS normally preserves the day number. If the input is the last day of its month, or if the target month has fewer days, Oracle returns the last day of the target month. February 28, 2025 is the last day of that month, so adding three months returns May 31 rather than May 28.

NEXT_DAY returns the first named weekday strictly later than the input. It also preserves the input's hours, minutes, and seconds. The weekday name must be valid in the session's date language, so an English literal such as 'MONDAY' introduces an NLS dependency in a multilingual deployment.

These four functions answer different calendar questions. The following statement formats returned dates explicitly so its output remains predictable while leaving the numeric MONTHS_BETWEEN result as a number:

SELECT TO_CHAR(
         LAST_DAY(DATE '2025-02-10'),
         'YYYY-MM-DD'
       ) AS month_end,
       MONTHS_BETWEEN(
         DATE '2025-03-31',
         DATE '2025-02-28'
       ) AS months_apart,
       TO_CHAR(
         TRUNC(DATE '2025-09-20', 'MONTH'),
         'YYYY-MM-DD'
       ) AS month_start,
       TO_CHAR(
         ROUND(DATE '2025-09-20', 'MONTH'),
         'YYYY-MM-DD'
       ) AS rounded_month
FROM   dual;

LAST_DAY finds February 28 in this non-leap year. MONTHS_BETWEEN returns an integer because both arguments are the last days of their months. When the day numbers differ and the values are not both month-end dates, Oracle can calculate a fractional result on a 31-day basis and include the difference between stored times.

Datetime TRUNC(..., 'MONTH') returns the first day of the input month at midnight. Datetime ROUND(..., 'MONTH') applies Oracle's calendar rounding rule, so September 20 rounds to October 1. These are datetime models, not the numeric precision arguments introduced in Lesson 6. Applying either function in a SELECT expression returns a derived value and does not update the stored date.

Format values explicitly when a report requires stable output:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS database_date,
       TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24:MI:SS') AS session_date,
       TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') AS database_timestamp
FROM   dual;

The result changes whenever the query runs, so a fixed example date should not be presented as current output. Oracle evaluates current-system datetime functions once for each SQL statement. Differences between the displayed values can reflect the session and database host time zones, not widely separated evaluation times.

• An Oracle DATE stores date and time through seconds, independent of how a client displays the value.
• Explicit TO_DATE and TO_CHAR models prevent accidental dependencies on session defaults.
• Adding a number to a DATE adjusts days; subtracting two DATE values returns elapsed days and can include fractions.
• ADD_MONTHS applies a last-day rule, while NEXT_DAY finds a named weekday strictly later than the input.
• Current-date functions differ by return datatype and by whether they use the database host or session time-zone context.
• Use timestamps and time-zone-aware datatypes when the application requires fractional seconds or time-zone information.