A Simple Math Formula to Get Special Dates

Introduction

It is not rare to calculate the special date on database development. For instance, which date is the last Thursday in a month. Although the developer can develop this systematically by control blocks with the system provided date functions, this method is not efficient. In this article, we introduce a math formula to calculate this kind of special date without using control blocks.

Background

Given two input date A and B and the question is to which date is the nearest weekday B to the input date A. For instance, we would like to know which date is the nearest Thursday to the Oct 19 2012.

Currently, most of popular relation database provide basic date functions such as data add, date diff functions. But they do not have direct functions to calculate the above special dates. The brute force is to determine Oct 19 2012 is Friday, then check the previous Thursday and the next Thursday whichever is closer to Oct 19. In this implementation we usually use if-else block to maked the decision.

Implementation

In this article, we use oracle as the tool to demonstrate our idea .

Basically, the idea is to change the input date B to 1 to 7 number representation and use the modular function to calculate how many days to the previous Thursday and next Thursday from the input date A . Since Oracle uses 1-7 to represent the Sunday to Monday, to focus on our main idea, We would use the 1-7 to represent the request date B as well. For example, 5 represents the Thursday.

//   1). minus_day  := mod((inputDate - days - 7), 7);
// 
//   2). add_day  := mod((inputDate - days + 7), 7);

The inputDate and the days above are the 1-7 representation of the input date A and B. 7 is the
length of the days in a week. Then we use the modular function to calculate the add_days and minus_day which are the days difference to the previous and next B respectively from the input date A.

The minus_days could be negative meaning the nearest date is the back date and the add_days is positive meaning those days needed to be added from the input date B to reach the next day B.

Then the formular 3) is to caculate which one of them is the smallest difference from the input Date A .

//  3). diff_day    := (1- floor(abs(add_day)/3.5)) * add_day + (1-floor(abs(minus_day)/3.5)) * minus_day;

Then we just simply add this diff_date to the inputDate to the get the nearest date.

Conclusion

The formula is useful in a certain extension and we can use it to calculate other dates. For instance, which date is the Monday in the second week of a input month.

In addition, we can also apply the formula to other languages such as C/C++, Java, etc as long as they has some basic date functions since not all of the system using the database.