Solved

Adding 1 Business Day to a Date

Posted on 2006-06-28
4
566 Views
Last Modified: 2010-08-05
I have a table that contains all of the transactions for all previous dates (it is extracted at month end).  

The transactions have a transaction date - but they are NOT posted until the next business day.     I need to create a report listing all transactions for the last posting month (transaction date + 1 business day).  I have  the basic code to add one day to the transaction date to get the posting date - but I need to BUSINESS days.  If the day that is calculated is a weekend or holiday, I need to find the next working day as the posting date.  Any ideas on a simple way to do this?   The only thing I've come up with is to build a table of all possible transaction dates and have another column that contains the posting date.

SELECT       convert(char(10),Tran_Date,101) as Transaction_Date,
      convert(char(10),DATEADD(d,1, Tran_Date),101) as Posting_Date,
      Tran_Amount
FROM    MONTHLY.dbo.DDA_Tran DDA_Tran
WHERE        DATEDIFF(mm, DATEADD(d,1, Tran_Date), GETDATE()) = 1
ORDER BY DDA_Tran.Tran_Date
0
Comment
Question by:Angela_Wilcox
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
skacore earned 250 total points
Comment Utility
Building a secondary table is probably what you'll have to do.  SQL won't know what days are holidays.  We have a calendar table for this purpose.
0
 
LVL 2

Expert Comment

by:MyersA
Comment Utility
Hi Angela_Wilcox,

You can use the DATENAME function:

IF DATENAME(WEEKDAY, @date) = ('saturday')
   BEGIN
     SELECT dateadd(day, 2, @date)
   END
ELSE IF DATENAME(WEEKDAY, @date) = ('sunday')
   BEGIN
     SELECT dateadd(day, 1, @date)
   END
0
 
LVL 2

Expert Comment

by:MyersA
Comment Utility
What I did was similar to skacore's suggestion.
I had a [calendar] table with all the dates of the year, and an [isHoliday] field. Then I'd run an Update query (using the query I posted) to update the dates that occurred on a weekend. And then I'd manually update the table with the other holidays.
Then I'd look up the date to the table and see if it's a holiday.
0
 

Author Comment

by:Angela_Wilcox
Comment Utility
I ended up creating the holiday table.  This solution from devx gives you a great jump on doing that.

http://www.devx.com/gethelpon/10MinuteSolution/16528

Understanding and Calculating Dates
The article explains how to automatically calculate when the next (or next N) business days are from a given date by calculating and storing this type of information in SQL Server. You will learn more about how SQL Server handles dates and about its functions.

by Joe Lax, SQL Server Pro


The article explains how to automatically calculate when the next (or next N) business days are from a given date by calculating and storing this type of information in SQL Server. You will learn more about how SQL Server handles dates and about its functions.
by Joe Lax, SQL Server Pro  
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:

Create a table of days in your calendar.

Populate the table with a row for each day in the calendar.

Set the holidays correctly.

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_ground
from shipments
where
shipdate = '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

Joe Lax has spent the last 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. Joe is also a MCSE and an MCT. Recently, he has started to learn Oracle, which affords him no end of fun.
 
SQL  
Listing 1: Date Functions Listing  
Here are some examples of SQL Server date functions in action.  
select getutcdate() as 'Here is the GMT Time'

select getdate() as 'Here is the current date and time'

select datediff(mi,getutcdate(),getdate()) as
'The difference between GMT and local time in minutes using datediff'

select datediff(dd,getdate(),'April 15,2001') as 'Number of days left to File'

select dateadd(dd,3,getdate()) as 'Three Days from Now is'

select datepart(yy,getdate()) as 'The year'


OUTPUT

Here is the GMT Time                                  
------------------------------------------------------
2001-04-12 22:29:05.977

Here is the current date and time                      
------------------------------------------------------
2001-04-12 18:29:05.977

The difference between GMT and local time in minutes using datediff
-------------------------------------------------------------------
-240

Number of days left to File
---------------------------
3

Three Days from Now is                                
------------------------------------------------------
2001-04-15 18:29:05.977

The year    
-----------
2001
 
SQL  
Listing 2: Day Planner Listing  
This code creates the necessary table of days in your calendar.  
if exists (select * from INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and
   table_name = 'shipments' and table_type = 'BASE TABLE')
   drop table shipments
go
-- create the table
create table shipments
(
   shipdate    datetime    not null,
   weekend     int      not null,
   holiday      int      not null,
   descript      varchar(30)   null,
   nextday      datetime   not null,
   twoday      datetime   not null,
   ups_ground   datetime   not null,
   constraint      pk_shipments primary key (shipdate)
)
go
 
SQL  
Listing 3: Day by Day  
This code creates a row for each calendar day  
-- fill the table up with starting values
declare @myday datetime
select @myday = '01/01/2001'

while @myday < '01/10/2005'
begin
   
   INSERT INTO [shipments]([shipdate],    [weekend],    
            [holiday],    [nextday],    [twoday],    [ups_ground])
   select         @myday,       case datepart(dw,@myday)
                  when 1 then 1
                  when 7 then 1
                  else 0 end,
            0,      @myday,      @myday,      @myday        

   select @myday = dateadd(dd,1,@myday)
end
 
SQL  
Listing 4: Identifying Holidays Listing  
This code enters the proper holidays.  
update shipments
set holiday = 1, descript = 'Christmas'
where
datepart(m,shipdate) = 12 and
datepart(dd,shipdate) = 25

update shipments
set holiday = 1, descript = 'New Years'
where
datepart(m,shipdate) = 1 and
datepart(dd,shipdate) = 1

update shipments
set holiday = 1, descript = 'July 4th'
where
datepart(m,shipdate) = 7 and
datepart(dd,shipdate) = 4
 
 
Algorithm SQL Statement
"Choose the first day...  select min(shipdate)from shipments
 
...that is two days or more greater than April 16, 2001  where datediff(dd,'April 16,2001',shipdate >= 2
 
...and is not a holiday or weekday."  and holiday = 0and weekend = 0
 
Table 1: SQL Translation Here is the SQL Server equivalent of an algorithm that calculates arrival dates.  

SQL  
Listing 5: You Want it When?  
Here's the code for each of the shipment types stored in the table.  
-- nextday
update shipments
set nextday =
(select min(shipdate)
from shipments m
where m.shipdate > shipments.shipdate and
m.weekend = 0 and
m.holiday = 0
)
where shipments.shipdate < '01/01/2005'

-- twoday
update shipments
set twoday =
(select min(shipdate)
from shipments m
where datediff(dd,shipments.shipdate,m.shipdate ) >= 2 and
m.weekend = 0 and
m.holiday = 0
)
where shipments.shipdate < '01/01/2005'

-- ups_ground
update shipments
set ups_ground =
(select min(shipdate)
from shipments m
where datediff(dd,shipments.shipdate,m.shipdate ) >= 5 and
m.weekend = 0 and
m.holiday = 0
)
where shipments.shipdate < '01/01/2005'
go
select *
from shipments
 
DevX is a division of Jupitermedia Corporation
© Copyright 2005 Jupitermedia Corporation. All Rights Reserved. Legal Notices
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now