Skip to Main Content
  • Questions
  • subtracting/adding day to dates but not counting sat and sun

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Voltaire.

Asked: January 19, 2017 - 3:09 pm UTC

Last updated: January 20, 2017 - 10:17 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

I have this user-defined function in T-Sql that will return a business day date (excluding Sat/Sun) based on add or subtract days.
Need help on how to convert this to an Oracle function.

CREATE FUNCTION [dbo].[businessDaysAdd]
(
@bDays int , -- number of business days to add sub.
@d datetime -- variable to hold the date you want to add sub. days to)
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @nDate datetime, @addsub int
SET @ndate = @d
IF @bdays > 0
SET @addsub = 1
ELSE
SET @addsub = -1
WHILE @bdays <> 0 -- Keep adding/subtracting a day until @bdays becomes 0
BEGIN
SELECT @ndate = dateadd(day,1*@addsub,@ndate)
SELECT @bdays =
CASE datepart(weekday,@ndate)-- ignore if it is Sat or Sunday
WHEN 7 THEN @bdays
WHEN 1 THEN @bdays
ELSE @bdays - 1*@addsub
END
END
RETURN @nDate
END

and Chris said...

Ug, T-SQL is ugly! ;)

Personally I'd create a table of dates storing whether or not each is a working business day. Then you can use this to find a day +/- N working days from today:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9532878900346980506



Is this answer out of date? If it is, please let us know via a Comment