When Comparing Dates in SQL

This article discusses the problematic use of BETWEEN when comparing dates in SQL and shows a better method for date range comparison.

Note: All the below SQL is written for Oracle Database.

Lets say that we need SQL to query for all activities (S_EVT_ACT) that has a Start Date (TODO_PLAN_START_DT) between 01-Jan-2010 and 31-Mar-2010. If the SQL was written as below this would be incorrect:

SELECT ACTIVITY_UID
FROM S_EVT_ACT
WHERE TODO_PLAN_START_DT BETWEEN TO_DATE('01-JAN-10') AND TO_DATE('31-MAR-10');

The problem is that the date specified as '01-JAN-10' is really saying: 01-JAN-10 12:00 AM and '31-MAR-10' is really saying: 31-MAR-10 12:00 AM. If you had an activity with TODO_PLAN_START_DT = 31-MAR-10 09:50 AM this activity would not be included. The above SQL will only look for activities that are on or before 12AM on 31-MAR-10 - which is actually excluding all of 31-MAR-10. Unless you had an activity which started at 12AM.

The correct way to write the SQL is always like this:

SELECT ACTIVITY_UID
FROM S_EVT_ACT
WHERE TODO_PLAN_START_DT >= TO_DATE('01-JAN-10')
AND TODO_PLAN_START_DT < TO_DATE('31-MAR-10) + 1;

This will ensure that all of 01-JAN-10 is included and also that all of 31-MAR-10 will be included in the range.

Converting UTC Dates

In Siebel we often use UTC dates for field types. We can determine if a Siebel BC Field's column is stored in UTC because it will have Type: DTYPE_UTCDATETIME.

So when we need to write SQL as above for a Siebel Field's column that is stored in UTC we would need to convert the UTC stored column to ensure we get the correct range. So lets say we are in Australian Eastern Standard Time (like me) we could write the above SQL like this if TODO_PLAN_START_DT is stored in UTC:

SELECT ACTIVITY_UID
FROM S_EVT_ACT
WHERE CAST((FROM_TZ(CAST(TODO_PLAN_START_DT AS TIMESTAMP), 'GMT') AT TIME ZONE 'Australia/NSW') AS DATE) <= TO_DATE('01-JAN-10')
AND CAST((FROM_TZ(CAST(TODO_PLAN_START_DT AS TIMESTAMP), 'GMT') AT TIME ZONE 'Australia/NSW') AS DATE) < TO_DATE('31-MAR-10) + 1;

However the above SQL may have problematic performance because it would not use any index on the TODO_PLAN_START_DT column, a full table scan would occur. This is because we have manipulated the predicate column (TODO_PLAN_START_DT) with functions which prevents index usage. It is always best practice to modify the non-predicate column so instead of converting the TODO_PLAN_START_DT from UTC to local time, we will convert our local time to UTC as such:

SELECT ACTIVITY_UID
FROM S_EVT_ACT
WHERE TODO_PLAN_START_DT <= CAST((FROM_TZ(CAST(TO_DATE('01-JAN-10') AS TIMESTAMP), 'Australia/NSW') AT TIME ZONE 'GMT') AS DATE)
AND TODO_PLAN_START_DT < CAST((FROM_TZ(CAST(TO_DATE('31-MAR-10') AS TIMESTAMP), 'Australia/NSW') AT TIME ZONE 'GMT') AS DATE) + 1;