Click here to Skip to main content
Click here to Skip to main content

Tagged as

A Simple Math Formula to Get Special Dates

, 8 Oct 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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.  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Harry Hai Huang
Software Developer
Canada Canada
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 8 Oct 2012
Article Copyright 2012 by Harry Hai Huang
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid