Today is January 29th and several errors have just come to our attention in code that has been working flawlessly for the past few years. Littered throughout our logs we are seeing ORA-01839: date not valid for month specified but this is code that hasn't changed in forever and it was fine yesterday!
Clearly this is something related to the fact it is a leap year so where did our code fail us? It turns out we opted to use the following approach to determine a shifting time window in a number of views.
<pre><code class="lang-sql">SELECT ....
.....
WHERE DT_COL BETWEEN (SYSDATE - INTERVAL '3' YEAR) AND SYSDATE
</code></pre>
That WHERE clause is simply subtracting 3 years off the current date's year and not offsetting for the fact there wasn't a 29th February three years ago. This little surprise was just sitting there waiting to bite us for that one day every four years and the fix for this is quite simple; just use ADD_MONTHS.
<pre><code class="lang-sql">SELECT ....
.....
WHERE DT_COL BETWEEN ADD_MONTHS(SYSDATE, - 36) AND SYSDATE
</code></pre>
Now it will correctly return the date from 3 years ago, February 28th. Lesson learned and at least in four year's time we won't be seeing this error again.

Today is January 29th and several errors have just come to our attention in code that has been working flawlessly for the past few years. Littered throughout our logs we are seeing **ORA-01839: date not valid for month specified** but this is code that hasn't changed in forever and it was fine yesterday!

Clearly this is something related to the fact it is a leap year so where did our code fail us? It turns out we opted to use the following approach to determine a shifting time window in a number of views.

```sql
SELECT ....
.....
WHERE  DT_COL BETWEEN (SYSDATE - INTERVAL '3' YEAR) AND SYSDATE
```

That WHERE clause is simply subtracting 3 years off the current date's year and not offsetting for the fact there wasn't a 29th February three years ago. This little surprise was just sitting there waiting to bite us for that one day every four years and the fix for this is quite simple; just use ADD\_MONTHS.

```sql
SELECT ....
.....
WHERE  DT_COL BETWEEN ADD_MONTHS(SYSDATE, - 36) AND SYSDATE
```

Now it will correctly return the date from 3 years ago, February 28th. Lesson learned and at least in four year's time we won't be seeing this error again.

Leaping into action to resolve a date error

I am a Certified Senior Oracle Developer and Data Architect with a passion for Oracle APEX and have several decades of experience crossing multiple industries such as Finance and Energy.
