SQL SERVER return a date through a UDF

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL SERVER return a date through a UDF

Author

Topic

kashif.special2005
Starting Member

10 Posts

Posted - 2013-06-12 : 06:55:19

Hi,

My name is Kashif, I am very new in sql server, I want create a UDF function that will return a previous date (exclude saturday, sunday and holiday)

Holiday's list in a table called 'tblHoliday'

Please help me to get desired result.

Thanks
Kashif

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 07:20:05

Can you elaborate the requirement?
Do want to Next Working day for a given input date?
List of dates in a month/year without weekends and holidays?

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 07:37:28

SELECT Date,Day,WeekDay FROM dbo.CalendarTable('2010-01-01','2010-02-28',1,0) -- give you the dates from 1st jan 2010 to 28th Feb 2010 without weekends....
--To exclude hoilidays list
SELECT Date FROM dbo.CalendarTable('2010-01-01','2010-02-28',1,0)
EXCEPT
SELECT date FROM tblHoliday;

For this UDF dbo.CalendarTable refer http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

--
Chandu

kashif.special2005
Starting Member

10 Posts

Posted - 2013-06-12 : 08:21:38

Hi,

Thanks for reply, please find below the example.

I have a table ‘tblHoliday’ in SQL Server like below.

Holiday_Date
4/18/2013
4/22/2013
4/24/2013
4/29/2013

And I want to create a UDF function that will give me previous business days excluding (Saturday, Sunday And Holiday).

Example,

Suppose I have created a UDF called ‘udfWorkingDays’, when I will execute it like below

Example 1:

udfWorkingDays ‘04/30/2013’, -1

It should return me (04/26/2013) – excluding weekends and holiday list

Example 2:

udfWorkingDays ‘04/30/2013’, -2

It should return me (04/25/2013) – excluding weekends and holiday list

Example 3:

udfWorkingDays ‘04/30/2013’, -3

It should return me (04/23/2013) – excluding weekends and holiday list

Thanks
Kashif

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 09:30:50

[code]CREATE TABLE Holiday_Date (Holiday DATE)
INSERT INTO Holiday_Date
SELECT '4/18/2013' union all
SELECT '4/22/2013' union all
SELECT '4/24/2013' union all
SELECT '4/29/2013'

GO
CREATE FUNCTION udfWorkingDays ( @Date DATE, @PrevDayNo INT)
RETURNS DATE
AS
BEGIN
DECLARE @PrevDay DATE;
;WITH CTE(PreviousDay) AS
(
SELECT @Date
UNION ALL
SELECT DATEADD ( DD, -1, PreviousDay)
FROM CTE
WHERE DATEDIFF( DD, PreviousDay, @Date) <=365
)
SELECT @PrevDay = PreviousDay
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY PreviousDay DESC) RN, PreviousDay
FROM CTE
WHERE DATENAME(WEEKDAY, PreviousDay) NOT IN ('Saturday', 'Sunday')
AND PreviousDay NOT IN (SELECT Holiday FROM Holiday_Date)
AND PreviousDay <= DATEADD(DD, -1, @Date)
) Temp
WHERE RN = ABS(@PrevDayNo)
OPTION (MAXRECURSION 0)

RETURN @PrevDay
END
GO

DECLARE @Date DATE = '04/30/2013', @PrevDayNo INT = -3
SELECT dbo.udfWorkingDays(@Date, @PrevDayNo)[/code]

--
Chandu

kashif.special2005
Starting Member

10 Posts

Posted - 2013-06-12 : 10:44:58

Hi,

Thank you so much, it is working fine

Thanks once again.
Kashif

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 00:26:08

quote:
Originally posted by kashif.special2005

Hi,

Thank you so much, it is working fine

Thanks once again.
Kashif

welcome

--
Chandu

kashif.special2005
Starting Member

10 Posts

Posted - 2013-06-14 : 09:08:12

Hi,

The function is working fine, however I want some modification in the function, like

When I am passing 0 instead of -1 it is giving me NULL value, however I want that If I pass 0 as a parameter it should return passing date, if the passing date is not (saturday, sunday and holiday).

Example:

when I execute below query it is giving me NULL value, however it should return pass date that is '05/30/2013' (execluding saturday, sunday and holiday)

DECLARE @Date datetime

set @Date=udfWorkingDays('05/30/2013',0)

select * from dbo.HWDomain_Holiday_Dates
select @Date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources