We have a holiday weekend coming up for us here in the USA. That gave me an idea for a topic we don’t talk about a lot. Many times our database applications have a requirement to be aware of the calendar. We need to know such things as standard business days, holidays, vacation days, shipping days. Ultimately, our database systems and database mining systems often need to be calendar aware.
Many times we want to provide our system with a calendar representing the days when business may occur. We can often project future dates based on the day of the week. We can say, for instance, that Monday is a non-working date for our business, and thus exclude all Mondays. This is something that may be done programmatically. What is more difficult to determine are those days that are less predictable. If you know an algorithm for a holiday, you can establish it easily. If a holiday always falls on the same day of the year, that may be managed programmatically as well. What becomes more problematic are those dates that have no predictable characteristic. Vacation time, or sick days for individuals are not readily entered into a calendar without some sort of table to track time individuals have requested.
What is the first day of the week? This is a problem you may have to resolve when working with dates. Depending on your location or culture, the first day of the week may be Saturday or Sunday. As a business, you may prefer Monday to be the first day of the week. With SQL Server you can use date functions to return the day of the week for a date. The day of the week depends on a configuration setting for your database that specifies what day is the first day of the week.
So, to summarize, a database dealing with calendars and dates will often have a table, or tables, tracking dates that are not consistently repeated, or determined by an algorithm. The system will most likely have predictable dates as well that are combined with the dates from a calendar table, or entered into a calendar table on a scheduled basis, resulting in a list of dates when business is impacted.
Do you have a favorite date implementation for tracking calendar events? How about sharing your design in our comments.
Cheers,
Ben