QL Server provides many sophisticated functions to calculate dates. But knowing how to use them can often be a challenge. Suppose you want to calculate when a package is due to arrive based on the shipping date and the type of shipping chosen. Because many shipment types don’t include delivery on a weekend or holiday, you can’t simply use the DATEADD() function to add a given number of days to the shipment date.

Using two-day shipping as an example, here’s how I’d describe the algorithm you need: “Choose the first day that is two or more days greater than the shipping date that is not a weekend or holiday.” That is, in fact, the way that you would do it manually. But how do you translate that instruction to SQL Server?

This 10-Minute Solution discusses a very common problem?calculating when the next (or next N) business days are from a given date. I’ll show you how to automate the process by calculating and storing this type of information in SQL Server. In doing so, you will learn more about how SQL Server handles dates and about its functions that manipulate them.



How do you use SQL Server’s date functions to calculate something as seemingly simple as “the next business day”?



Here are the steps to calculate the dates accurately:

1. Create a table of days in your calendar.
   
2. Populate the table with a row for each day in the calendar.
   
3. Set the holidays correctly.
   
4. Calculate the arrival dates.

Dates in SQL Server
Before showing you how to calculate dates automatically, let me explain a little about dates in general. SQL Server uses two data types to store both date and time information:

• The datetime data type uses eight bytes of storage. It can accept values ranging from January 1, 1753, through December 31, 9999, and is accurate to within 3.3 milliseconds.
  
• The smalldatetime data type uses only four bytes of storage. However, it can only accept values from January 1, 1900, through June 6, 2079, and is only accurate to within one minute.

SQL Server does not come with any data type that stores just date or time information. If you enter a date without specifying a time, such as “01/05/2001,” the default time selected will be midnight.

Date Functions in SQL Server
SQL Server contains some very useful functions to manipulate dates. GETDATE() returns the current date and time. SQL Server 2000 has also added the GETUTCDATE() function that returns the date and time in GMT.

To compare dates, you can use the DATEDIFF() function. This function takes three arguments: the date “part” to return the difference in and the two dates you want to compare. For example,

select datediff (dd,getdate(),’April 16, 2001′)

returns the number of days you have to file your tax return. (In 2001 the due date for filing tax returns was April 16th; normally it’s the 15th.) If you wanted the result in hours, you would simply change the first argument from “dd” for dates to “hh” for hours.

The DATEADD() function allows you to add time to an existing date value. For example,

select dateadd(hh,3,getdate())

returns the date and time three hours from now. You can also obtain a particular part of a date by using the DATEPART() function. This means that

select datepart(dd,getdate())

returns the day of the month. See Listing 1 for some examples of these functions in action.

Now to solving the problem that this 10-Minute Solution raises!

Create a Table of Days in Your Calendar
First, create a table (see Listing 2) that holds the results of your calculations and actually helps solve your problem.

The weekend and holiday field contains 1 if the day is a weekend or holiday, respectively, and 0 otherwise. The nextday, twoday, and ups_ground columns store when a package is due to arrive, based on the shipment type.

Populate the Table with a Row for Each Day
Your next step is to insert a row for each day (calendar days, of course, not just business days) for a reasonable range of dates. In the code below, I’ve chosen to insert dates for four years?2001 through 2005. If you look closely, I’ve actually inserted ten rows for the year 2005. Why? As you will soon see, I actually use the rows in the table to find “the first day that is two or more days that is not a holiday or weekend.” Because I want to find the arrival dates for “12/31/2004,” I have to include in the table the rows for the beginning of 2005.

I use the CASE statement to set the weekend field correctly. Datepart(dw,@myday) returns the day of the week of the variable @myday. If it’s either 7 or 1 (i.e., Saturday or Sunday) then the case statement evaluates to a 1, otherwise it inserts a 0 into that column (see Listing 3).

Set the Holidays Correctly
At this point, entering the holidays really means updating the correct rows by changing the holiday column to 1 for each day that’s a holiday. In the code below I’ve updated the table for three sample holidays. As you can see, I don’t have to write an update statement for every July 4th in my table. Instead, by using the DATEPART() function I can update every row where the month equals July and the day equals the fourth of the month (see Listing 4).

Calculate the Arrival Dates
Now that you have the shipments table properly set up, it’s easy to calculate the arrival dates. Remember, the algorithm for two-day shipping: “Choose the first day that is two or more days greater than the shipping date that is not a weekend or holiday.”

Using April 16, 2001 as the end date, Table 1 shows you how to write a query to calculate when it will arrive.

Now instead of calculating it for one day at a time, let’s calculate it for all the rows in the table and store the results in the proper columns?i.e., “nextday,” “twoday,” etc. (as shown in Listing 5).

Once you’ve done all this, obtaining the arrival date for items shipped on say May 1st is as simple as this:

select nextday,twoday,ups_groundfrom shipmentswhereshipdate = ’05/01/2001’nextday                  twoday                   ups_ground———————–  ———————–  ———————-2001-05-02 00:00:00.000  2001-05-03 00:00:00.000  2001-05-07 0:00:00.000