SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday

July 23, 2007 by pinaldave

While reading ColdFusion blog of Ben Nadel Getting the Previous Day In ColdFusion, Excluding Saturday And Sunday, I realize that I use similar function on my SQL Server Database. This function excludes the Weekends (Saturday and Sunday), and it gets previous as well as next work day.

Script to create function to get previous and next working day.
CREATE FUNCTION dbo.udf_GetPrevNextWorkDay (@dtDate DATETIME, @strPrevNext VARCHAR(10)) RETURNS DATETIME AS BEGIN DECLARE @intDay INT DECLARE @rtResult DATETIME SET @intDay = DATEPART(weekday,@dtDate) --To find Previous working day IF @strPrevNext = 'Previous' IF @intDay = 1 SET @rtResult = DATEADD(d,-2,@dtDate) ELSE IF @intDay = 2 SET @rtResult = DATEADD(d,-3,@dtDate) ELSE SET @rtResult = DATEADD(d,-1,@dtDate) --To find Next working day ELSE IF @strPrevNext = 'Next' IF @intDay = 6 SET @rtResult = DATEADD(d,3,@dtDate) ELSE IF @intDay = 7 SET @rtResult = DATEADD(d,2,@dtDate) ELSE SET @rtResult = DATEADD(d,1,@dtDate) --Default case returns date passed to function ELSE SET @rtResult = @dtDate RETURN @rtResult END GO
Following examples will execute above function and provide desired results.
SELECT dbo.udf_GetPrevNextWorkDay('1/1/2007','Previous') SELECT dbo.udf_GetPrevNextWorkDay('1/1/2007','Next') SELECT dbo.udf_GetPrevNextWorkDay('12/31/2007','Previous') SELECT dbo.udf_GetPrevNextWorkDay('12/31/2007','Next')

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 12 Comments

12 Responses

on February 2, 2008 at 3:55 am Chitra

Hi Pianl,
Hello, how are you? I need help with a sql query that I am trying to write. I am trying to convert a date into a day in a query where a field called eventstartdttm is a varchar field that holds date and time

for example if substring(eventstartdttm, 0,9) returns 30th Jan 2008 then I need it to say Wednesday. Is there a function that can help me do that? Thanks

Warm Regards,
Chitra
on February 11, 2008 at 1:40 pm Naomi

Try it:
select DATENAME(dw ,convert(datetime, substring(eventstartdttm, 0,9),3))
– 3 for format dd/MM/yy

Good-Luck!
on February 28, 2008 at 2:33 am Ying

I am a newbie in SQL
I am trying to structure a query wherein
I need to display data where the Qtrly end dates(3/31,6/30/9/30/12/31) have been moved for vendors.
also The Table has multiple records for the same vedor ending in 3/31 and I need to take a max

When I tried using date diff between Quarters for different Years it does not work.. any help here is appreciated
on July 17, 2008 at 6:45 pm Rahul

Hi,

I am looking for a function through which I can calculate a particular working day. let say I want to know the 8th working day from todays date.

Any help??? its urgent..

Thanks in advance.

Rahul
on July 18, 2008 at 2:08 am Imran Mohammed

@RAHUL

You mean this ??

select datename(dd,getdate()+8)
Result: 25

select datename (dw,getdate() +8)
Result : Friday –(if today is July 17, Thursday)
on November 20, 2008 at 8:21 pm Anthony SMith

This is a great function and I really like how this function works . Is there any way to account for Holidays? I have seen something where one could enter Holidays in a table (eg – tblHolidays –>brilliant naming huh?). When the workday falls on the date in the table, the function would then go onto the next working day.

How would I modify this Function to do this?
on February 24, 2009 at 3:46 pm Rajesh

hi Pianl,

I need a help from u.

I am having a field name issueDate datatype as smalldatetime.
I need this field and also the previous date

ex: Item id ItemName IssueDate
1023 XXXX 12/02/2009
1023 XXXX 13/02/2009

I need the answer is diff between the 13/02/2009(LastDate) – 12/02/2009(previous date)
on February 24, 2009 at 5:39 pm SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday [...]
on March 13, 2009 at 9:41 pm George

Simple and good…!!!

Works well for me…
on March 13, 2009 at 9:46 pm George

select DATEDIFF ( day , ‘02/13/2009′ , ‘02/12/2009′ )
select DATEDIFF ( hour , ‘02/13/2009′ , ‘02/12/2009′ )
on May 27, 2009 at 9:20 am robert

how can i get the balance which the only given is the breakdown of payments, transaction amount and transaction date and i will use the transaction date as parameters.

for example(table form): this shoul be the output:

customer trans_amt payments trans_date balance
robert 200 10 1-1-09 190
robert 200 50 1-5-09 140
robert 200 60 1-7-09 80

i want to get balance for every trans_date

please help me
thanks

robert
on May 27, 2009 at 9:22 am robert

how can i get the balance which the only given is the breakdown of payments, transaction amount and transaction date and i will use the transaction date as parameters.

for example(table form): this shoul be the output:

customer—-trans_amt—-payments—trans_date—-balance
robert——–200————–10———-1-1-09———-190
robert——–200————–50———-1-5-09———-140
robert——–200————–60———-1-7-09———-80

i want to get balance for every trans_date

please help me
thanks

robert