Leap years are those things that for the most part go unnoticed, but occasionally in niche circumstances they can bite you when you least expect it.
Consider the following example that popped up on an AskTom question recently. I’ll start with a simple requirement:
For a user provided date, show me the total sales for that date.
SQL> variable str varchar2(14)
SQL> exec :str := '13/JAN/2023';
PL/SQL procedure successfully completed.
SQL> select sum(amount)
2 from sales
3 where txn_date = to_date(:str);
SUM(AMOUNT)
-----------
48312.26
So far so good. But a common requirement when it comes to retail is comparing results across time spans. In this case, we want to compare the sales with the user provided date with the same day last year. The challenge then becomes “How do we derive last year from the date provided that is this year?”
A first stab at this might be a simple subtraction. Most years have 365 days, so I can simple use that to get last year.
SQL> select txn_date, sum(amount)
2 from sales
3 where txn_date in (to_date(:str),to_date(:str)-365)
4 group by txn_date
5 order by 1;
TXN_DATE SUM(AMOUNT)
--------- -----------
13-JAN-22 56459.47
13-JAN-23 48312.26
Given that this post is called “Bitten by the leap year?” it is fairly obvious where this approach is going to fail. The same calculation is fine within a leap year
SQL> exec :str := '13/JAN/2024';
PL/SQL procedure successfully completed.
SQL> select
2 to_date(:str) ty,
3 to_date(:str)-365 ly
4 from dual;
TY LY
--------- ---------
13-JAN-24 13-JAN-23
but when the year the follows a leap year is nominated, things do not look so good.
SQL> select
2 to_date(:str) ty,
3 to_date(:str)-365 ly
4 from dual;
TY LY
--------- ---------
13-JAN-25 14-JAN-24
A common technique I’ve seen to address this shortfall is to use the ADD_MONTHS function. As we all know, there are 12 months in a year, and since ADD_MONTHS can take positive or negative values, it is easy to go back one year, and this also works for the problematic leap year:
SQL> exec :str := '13/JAN/2024';
PL/SQL procedure successfully completed.
SQL> select
2 to_date(:str) ty,
3 add_months(to_date(:str),-12) ly
4 from dual;
TY LY
--------- ---------
13-JAN-24 13-JAN-23
SQL>
SQL> exec :str := '13/JAN/2025';
PL/SQL procedure successfully completed.
SQL> select
2 to_date(:str) ty,
3 add_months(to_date(:str),-12) ly
4 from dual;
TY LY
--------- ---------
13-JAN-25 13-JAN-24
We have improved on the previous expression which returned incorrect results during an entire year, but however, even ADD_MONTHS is not a 100% solution. Consider what happens for one particular day of the year
SQL> exec :str := '28/FEB/2025';
PL/SQL procedure successfully completed.
SQL> select
2 to_date(:str) ty,
3 add_months(to_date(:str),-12) ly
4 from dual;
TY LY
--------- ---------
28-FEB-25 29-FEB-24
As per the docs, when an ADD_MONTHS take an input which is the last day of the month, the resultant value will also be “nudged” to be the last day of the month. After all, the ADD_MONTHS function (as its name suggests) is primarily concerned around whole months, so in most circumstances, staying on month boundaries will be the best thing to do. It doesn’t know that in our particular use case, we’re trying to mimic year functionality not months.
Perhaps avoiding the concept of months at all is hence a better option. There are year based intervals, so what happens when I try that:
SQL> exec :str := '28/FEB/2023';
PL/SQL procedure successfully completed.
SQL> select
2 to_date(:str) ty,
3 to_date(:str) - numtoyminterval (1,'year') ly
4 from dual;
TY LY
--------- ---------
28-FEB-23 28-FEB-22
That looks good but unfortunately, you can still hit some speed bumps along the way with this approach.
SQL> exec :str := '29/FEB/2024';
PL/SQL procedure successfully completed.
SQL> select
2 to_date(:str) ty,
3 to_date(:str) - numtoyminterval (1,'year') ly
4 from dual;
to_date(:str) - numtoyminterval (1,'year') ly
*
ERROR at line 3:
ORA-01839: date not valid for month specified
Of course, with any of the above approaches, we could explicitly code around the issue with a check of these boundary conditions, for example:
SQL> exec :str := '28/FEB/2025';
PL/SQL procedure successfully completed.
SQL> select
2 to_date(:str) ty,
3 case when
4 substr(:str,1,6) = '28/FEB' and
5 to_char(to_date(to_char(add_months(to_date(:str),-12),'YYYY')||'1231','yyyymmdd'),'ddd') = 366
6 then
7 add_months(to_date(:str),-12)-1
8 else
9 add_months(to_date(:str),-12)
10 end ly
11 from dual;
TY LY
--------- ---------
28-FEB-25 28-FEB-24
but my advice here is to not use any of these approaches when it comes to dealing with these comparison scenarios. To justify why, let me return to our sales data.
SQL> exec :str := '07/APR/2023';
PL/SQL procedure successfully completed.
SQL> select txn_date, sum(amount)
2 from sales
3 where txn_date in (to_date(:str),to_date(:str)-365)
4 group by txn_date
5 order by 1;
TXN_DATE SUM(AMOUNT)
--------- -----------
07-APR-22 44358.30
07-APR-23 2632.06
If I am a business analyst and I see that result, should I be race into the CEO office with “Alert! Alert! We are missing are targets by a MASSIVE amount!”. If I did that, I’d probably get laughed out of the office after a couple of minutes, because we would discover that April 7th 2023 was Good Friday, one of the quietest retail days in the many parts of the world that observe Easter traditions.
Therein lies the fundamental issue. It is not about day, month, or yearly arithmetic. It is about the appropriate definition of how to compare dates across long spans of time (years, months etc). I probably do not want to compare weekdays with weekends, or working days with public holidays etc. I contend that the only way you can seriously do day-wise comparisons is to explicitly map the dates yourself and not rely on an expression.
We need a table for that, for example:
SQL> create table date_map
2 ( current_date date primary key,
3 last_year date,
4 why varchar2(100)
5 );
Table created.
SQL>
SQL> insert into date_map
2 values ('01/JAN/24','01/JAN/23','New Years Day');
1 row created.
SQL>
SQL> insert into date_map
2 values ('02/JAN/24','03/JAN/23','Tuesday');
1 row created.
SQL>
SQL> insert into date_map
2 values ('03/JAN/24','04/JAN/23','Wednesday');
1 row created.
SQL>
SQL> insert into date_map
2 values ('04/JAN/24','05/JAN/23','Thursday');
1 row created.
...
...
SQL> insert into date_map
2 values ('29/FEB/24','02/MAR/23','Closest Thursday');
1 row created.
...
...
SQL> insert into date_map
2 values ('29/MAR/24','07/APR/23','Good Friday');
1 row created.
We have now explicitly defined the relationships “this” year and “last” year and can then fold that into our queries
SQL> exec :str := '29/MAR/24';
PL/SQL procedure successfully completed.
SQL> select s.txn_date, sum(s.amount)
2 from sales s,
3 date_map d
4 where s.txn_date in (d.current_date, d.last_year)
5 and d.current_date = to_date(:str)
6 group by s.txn_date
7 order by 1;
TXN_DATE SUM(S.AMOUNT)
--------- -------------
07-APR-23 2632.06
29-MAR-24 3264.08
You might be thinking that is a painful exercise, but you only need to spend that effort once and populate (say) the next 100 years and forget about the problem forever. Even if you just matched the day of weeks and the main annual holidays you’ll be in a much better position than relying on expressions that do not take those things into account.
PS: Don’t flame me for being lazy and leaving out the format mask for my TO_DATE function. We’re focussing on the date arithmetic here, not the assignment of the date itself.
Learning is not a spectator sport 
Leave a reply to Mark Russell Brown Cancel reply