Adding 1 Business Day to a Date
Posted on 2006-06-28
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,
FROM MONTHLY.dbo.DDA_Tran DDA_Tran
WHERE DATEDIFF(mm, DATEADD(d,1, Tran_Date), GETDATE()) = 1
ORDER BY DDA_Tran.Tran_Date