Issue with comparing dates in SQL

Question

I have a table with the following columns

effective_start_date
effective_end_date
person_id

This is on Oracle DB.
I am writing a query to get me the person active for the current sysdate, using the following query.

select startDate, endDate, sysdate
from dateTable
where sysdate between startDate and endDate;

The issue is that this where clause never seems to get applied. The result contains rows that dont satisfy this condition. Any suggestions on what i might be missing.

All three columns are of `Date` type..? Make sure that u have them equal.. — Hiren Pandya, Apr 18 '13 at 11:32
Query is perfect and should work. Kindly provides some results to show the issue — Lokesh, Apr 18 '13 at 11:40
People have been using BETWEEN with dates (including SYSDATE) for literally decades. If it's not working the way you expect it's because there is a problem with your data. Remember that dates in Oracle always contain a time element. — APC, Apr 18 '13 at 12:17
Can you verify that the date values in the table have sensible years, in the expected century, by selecting `to_char(start_date, 'YYYY-MM-DD HH24:MI:SS')` etc? — Alex Poole, Apr 18 '13 at 12:25
@AlexPoole, ah thanks. That helped verify the year. Dates were displayed as 12-DEC-31, where 'year' 12 was not 2012, but 4712, which obv is greater that sysdate. Thanks. — bhates, Apr 18 '13 at 12:29

score 0 · Accepted Answer · answered Apr 18 '13 at 13:41

Your query looks fine, and as APC suggests this is a common pattern. Which means you have a data problem. Displaying the full year will show why the unexpected records are matching, and any 'bad' dates - e.g. using unexpected centuries, which is not an unusual problem when dates are manipulated using a YY format mask:

select to_char(startDate, 'YYYY-MM-DD HH24:MI:SS') as startDate,
    to_char(endDate, 'YYYY-MM-DD HH24:MI:SS') as endDate, sysdate
from dateTable
where sysdate between startDate and endDate;

In the comments you've already noted that this showed dates from 4712, which showed as just 12 using your YY-MON-DD date format mask. I haven't seen exactly that, it's more common to see 0012 or 1912, but a record with endDate in 4712 certainly ends after sysdate. It's probably worth verify all of the dates in the table, not just those that were flagged by that where clause.

Issue with comparing dates in SQL

1 Answers1