如何在Oracle SQL Developer中查询两个日期之间的数据

Hey there! Let's work through why your date range query is throwing errors. Since your database uses the DD-MON-RR format (like 13-DEC-10), the most common issues come from mixing string comparisons with actual date values, or not properly converting your input dates to Oracle's date type. Here's how to fix it:

1. Stop comparing dates as strings #

If you're writing something like:

SELECT * FROM your_table WHERE your_date BETWEEN '01-DEC-10' AND '31-DEC-10';

Oracle might treat those values as strings instead of dates, leading to incorrect comparisons (string ordering doesn't match date ordering!). Always work with date types directly.

2. Use Oracle's standard date literals #

Oracle recognizes the ISO date format (YYYY-MM-DD) with the DATE keyword, regardless of your database's NLS settings. This is the most reliable way:

SELECT *
FROM your_table
WHERE your_date_column BETWEEN DATE '2010-12-01' AND DATE '2010-12-31';

3. Convert strings to dates with TO_DATE() #

If you need to use the DD-MON-RR format in your query, explicitly convert your input strings to dates using the TO_DATE() function with the correct format mask:

SELECT *
FROM your_table
WHERE your_date_column BETWEEN TO_DATE('01-DEC-10', 'DD-MON-RR') 
                           AND TO_DATE('31-DEC-10', 'DD-MON-RR');

• The RR mask handles two-digit years: values 00-49 are treated as 2000-2049, and 50-99 as 1950-1999 (perfect for your 10 year value, which becomes 2010).

4. Avoid edge cases with BETWEEN #

If your date column includes time components (like 13-DEC-10 14:30:00), using BETWEEN might miss records from the end date that are after midnight. Instead, use a range that includes the start date and excludes the day after the end date:

SELECT *
FROM your_table
WHERE your_date_column >= DATE '2010-12-01'
  AND your_date_column < DATE '2011-01-01'; -- Excludes any time on Jan 1, 2011

This ensures you capture all records from December 2010, even those with non-midnight times.

5. (Optional) Check your NLS_DATE_FORMAT #

While not recommended for portable queries, if you want to use string literals without TO_DATE(), make sure your session's NLS_DATE_FORMAT matches the database's format. You can check it with:

SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';

But again, relying on this can cause issues if other users have different settings, so stick to date literals or TO_DATE() for consistency.

内容的提问来源于stack exchange，提问作者goonerboi