A date filter depends on session settings

Illustrative learning scenario; not a claimed client engagement. A fictional overnight export should include every event on 31 October. A string-to-date comparison behaves differently between developer sessions; after changing it to midnight equality, events later in the day still vanish. Oracle DATE includes hours, minutes and seconds.

Symptoms

Replace implicit date conversion and midnight equality with an explicit day range.

Diagnosis

1. Inspect the column datatype first: this fixture uses DATE, not a character column or TIMESTAMP WITH TIME ZONE.
2. Inspect NLS_DATE_FORMAT when investigating string conversion failures; do not change the entire session as the application fix.
3. Compare midnight and afternoon rows using a half-open range and verify that the following midnight is excluded.

Runnable minimal example

Oracle Database 19c SQL; read-only CTE fixture. Not executed on Oracle; expected output is documentation-reviewed.

WITH events AS (
  SELECT 1 id, DATE '2026-10-31' occurred_at FROM dual
  UNION ALL SELECT 2, DATE '2026-10-31' + 15/24 FROM dual
  UNION ALL SELECT 3, DATE '2026-11-01' FROM dual
)
SELECT id FROM events
WHERE occurred_at >= DATE '2026-10-31'
  AND occurred_at < DATE '2026-11-01'
ORDER BY id;

Expected output — not executed

ID
1
2

Fix and boundaries

Use typed date binds in application queries or ANSI DATE literals for fixed labs. Use >= start and < next_day for day coverage. For text supplied under a known contract, parse with an explicit format and validate input; do not rely on implicit conversion. Define the business time zone before calculating boundaries for zoned timestamps.

Why it works

ANSI literals have a fixed YYYY-MM-DD syntax independent of NLS_DATE_FORMAT. The lower bound includes midnight and later values; the exclusive upper bound avoids guessing a last representable second. Keeping the column bare can also preserve an index range access opportunity, though the optimizer still chooses the actual plan.

Prevention

Include midnight, afternoon and next-day boundary fixtures. Keep bind datatypes aligned with columns. Review plans with representative data rather than promising an index from a three-row example.

Practice exercise

Change the requirement to the whole month of October. Use 1 October and 1 November bounds, add a September row, and predict which identifiers remain.

Documentation

• Oracle datetime literals
• Oracle TO_DATE