Oracle date "Between" Query

sql

date-formatting

sql-queries

performance-optimization

byAlex Kataev·Sep 6, 2024

When filtering records in Oracle for a specific date range, employ BETWEEN and TO_DATE for precision, being sure to encapsulate the entire day, if necessary.

Example:

SELECT *
FROM your_table
WHERE your_date_column BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
                         AND TO_DATE('2023-01-31', 'YYYY-MM-DD') + 0.99999;

The above snippet fetches records where your_date_column lands within January 2023, covering the whole day up till 23:59:59 of the last date.

Dealing with time specifics

Don't exclude Cinderella

To ensure you include the whole of the final day in your set:

SELECT *
FROM your_table
WHERE your_date_column >= TO_DATE('2023-01-01', 'YYYY-MM-DD')
AND your_date_column < TO_DATE('2023-02-01', 'YYYY-MM-DD');

Why? This one-minute-past-midnight trick includes all events happening at the stroke of midnight, thereby including Cinderella's entire evening.

Boost your performance

If truncating the time from your query is a common occurrence, consider:

Building a function-based index on TRUNC(your_date_column)
Matching the date criteria in your query to your indexed expression

Control your session aesthetics

To consistently present dates without individual query adjustments:

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

Note: This does not alter date storage or comparative behavior but promises a better looking output.

Handling complex date scenarios

Wrangling timestamps

If your column uses timestamps and you seek to use 'YYYY-MM-DD' formatting:

SELECT *
FROM your_table
WHERE CAST(your_date_column AS DATE) BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
                                        AND TO_DATE('2023-01-31', 'YYYY-MM-DD') + 0.99999;

Attention: Casting to DATE will exclude the fractional seconds, a minor loss for major simplicity.

Type alignment

Check your query's data types align with the table's column types. Doing so can effectively optimize the utility of indexes.

New year, new boundaries

Stay vigilant while forming date range expressions, particularly for year-end queries:

-- Avoids missing the New Year's Eve party
WHERE your_date_column >= TO_DATE('2023-01-01', 'YYYY')
AND your_date_column < TO_DATE('2024-01-01', 'YYYY');

Sidestepping potential issues

Explicit > Implicit

Subdue implicit conversion conundrums by defining the date format:

TO_DATE('2023-01-01', 'YYYY-MM-DD HH24:MI:SS')

Today's special!

To employ today's date in boundaries, we use SYSDATE:

WHERE your_date_column >= TRUNC(SYSDATE) -- Start of today
AND your_date_column < TRUNC(SYSDATE) + 1; -- Start of tomorrow

Bonus: This helps avoid procrastination!

Don't truncate recklessly

While TRUNC can simplify your life by disregarding time-stamps, overuse may adversely impact performance, unless you've got function-based indexes backing you up.