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

SQL SERVER - UDF - Function to Get Previous And Next Work Day - Exclude Saturday and Sunday clock While reading ColdFusion blog of Ben Nadel Getting the Previous Day In ColdFusion, Excluding Saturday And Sunday, I realize that I use similar function in 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 a 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 the above function and provide the 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')

Let me know what you think of this blog post. I personally found this one of the most interesting functions related to daytime and worth bookmarking.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – UDF – Get the Day of the Week Function
Next Post
SQL SERVER – Data Warehousing Interview Questions and Answers – Introduction

Related Posts

16 Comments. Leave new

  • 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

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

    Good-Luck!

    Reply
  • 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

    Reply
  • 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

    Reply
  • Imran Mohammed
    July 18, 2008 2:08 am

    @RAHUL

    You mean this ??

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

    select datename (dw,getdate() +8)
    Result : Friday –(if today is July 17, Thursday)

    Reply
  • 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?

    Reply
    • Just came across the need to do this myself. I made the function just call itself again in the event that the previous/next date falls on a holiday.

      IF EXISTS(SELECT * FROM tblHolidays WHERE HolidayDate = @EndDate)
      BEGIN
      SELECT @EndDate = dbo.udf_GetPrevNextWorkDay(@EndDate, @strPrevNext)
      END

      Reply
  • 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)

    Reply
  • Simple and good…!!!

    Works well for me…

    Reply
  • select DATEDIFF ( day , ’02/13/2009′ , ’02/12/2009′ )
    select DATEDIFF ( hour , ’02/13/2009′ , ’02/12/2009′ )

    Reply
  • 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

    Reply
  • 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

    Reply
  • Built off this script to create the following udf that has option to exclude holidays and also to retrieve more than just 1 day prev/next:

    CREATE FUNCTION dbo.udfGetPrevNextWorkDay
    (
    @StartDate DATETIME,
    @bGetPrevious BIT = 0,
    @bExcludeHolidays BIT = 1,
    @MultipleDayCount INT = 0
    )
    RETURNS DATETIME
    AS
    BEGIN

    DECLARE @EndDate DATETIME, @Day NVARCHAR(10), @DayCount INT
    SET @Day = DATENAME(weekday, @StartDate)

    IF @MultipleDayCount > 0
    BEGIN
    DECLARE @MultipleDayIndex INT, @MultipleDayDate DATETIME
    SET @MultipleDayDate = @StartDate
    SET @MultipleDayIndex = 1

    WHILE @MultipleDayIndex < @MultipleDayCount
    BEGIN
    SELECT @MultipleDayDate = dbo.udfGetPrevNextWorkDay(@MultipleDayDate, @bGetPrevious, @bExcludeHolidays, 0)
    SET @MultipleDayIndex = @MultipleDayIndex + 1
    END
    RETURN @MultipleDayDate
    END

    –To find Previous working day
    IF @bGetPrevious = 1
    BEGIN
    SELECT @DayCount =
    CASE @Day WHEN 'Sunday' THEN -2
    WHEN 'Monday' THEN -3
    ELSE -1
    END
    END

    –To find Next working day
    IF @bGetPrevious = 0
    BEGIN
    SELECT @DayCount =
    CASE @Day WHEN 'Friday' THEN 3
    WHEN 'Saturday' THEN 2
    ELSE 1
    END
    END

    SET @EndDate = DATEADD(d,@DayCount,@StartDate)

    IF (@bExcludeHolidays = 1) AND (EXISTS(SELECT * FROM tblzcdIndexHoliday WHERE IndexHolidayCelebrationDate = @EndDate))
    BEGIN
    SELECT @EndDate = dbo.udfGetPrevNextWorkDay(@EndDate, @bGetPrevious, @bExcludeHolidays, 0)
    END

    RETURN ISNULL(@EndDate, @StartDate)
    END

    Reply
  • Script to calculate hollidays (i.e not using a table… Tricky part is Easter :)

    — Drop function if exists
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID(N'[dbo].[IsHolliday]’) AND xtype IN (N’FN’, N’IF’, N’TF’)) DROP FUNCTION [dbo].[IsHolliday]
    GO

    — Create function
    CREATE FUNCTION dbo.IsHolliday
    — +————————————————————————–+
    — | Function : IsHolliday |
    — | Author : Martin Grape |
    — | Version : 1.1 |
    — | Description : Returns 1 for holliday and 0 for all other days |
    — | Parameters : @IsHolliday – Date to check |
    — | Returns : 0 (workday) or 1 (holliday) |
    — | Other : This is calculation for Swedish hollidays |
    — | Example : SELECT dbo.IsHolliday(‘2010-04-04′) –> 1 |
    — +————————————————————————–+
    (
    @IsHolliday DATETIME
    )
    RETURNS INT
    AS
    BEGIN
    — Declare variables
    DECLARE @IsHollidayResult INT
    DECLARE @IsHollidayYear INT
    DECLARE @IsHollidayMonth INT
    DECLARE @IsHollidayDay INT
    DECLARE @IsHollidayMonthDay VARCHAR(5)
    DECLARE @IsHollidayEasterDate DATETIME

    — Declare variables
    SET @IsHollidayYear = YEAR(@IsHolliday)
    SET @IsHollidayMonth = MONTH(@IsHolliday)
    SET @IsHollidayDay = DAY(@IsHolliday)
    SET @IsHollidayMonthDay = RIGHT(CONVERT(CHAR(10), @IsHolliday, 120), 5)

    — Set starting values
    SET @IsHollidayResult = 0

    — Check for set dates (this is for Sweden)
    SET @IsHollidayResult = CASE WHEN @IsHollidayMonthDay IN (’01-01′, ’01-06′, ’05-01′, ’12-25′, ’12-26′) THEN 1 ELSE @IsHollidayResult END
    SET @IsHollidayResult = CASE WHEN @IsHollidayResult = 0 AND @IsHollidayMonthDay = ’06-06’ AND @IsHollidayYear > 2004 THEN 1 ELSE @IsHollidayResult END

    — Return if a set date
    IF @IsHollidayResult = 1 BEGIN
    RETURN @IsHollidayResult
    END

    — Calculate easter date
    SET @IsHollidayEasterDate = CONVERT(DATETIME, CONVERT(CHAR(4), @IsHollidayYear) + ‘-‘ +
    RIGHT(’00’ + CONVERT(VARCHAR, CONVERT(INT, ((((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + (((32 + 2 * ((CONVERT(INT, @IsHollidayYear / 100)) % 4) + 2 * (CONVERT(INT, (@IsHollidayYear % 100) / 4)) – (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) – ((@IsHollidayYear % 100) % 4)) % 7)) – 7 * (CONVERT(INT, ((@IsHollidayYear % 19) + 11 * (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + 22 * (((32 + 2 * ((CONVERT(INT, @IsHollidayYear / 100)) % 4) + 2 * (CONVERT(INT, (@IsHollidayYear % 100) / 4)) – (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) – ((@IsHollidayYear % 100) % 4)) % 7))) / 451)) + 114) / 31)), 2) + ‘-‘ +
    RIGHT(’00’ + CONVERT(VARCHAR, (((((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + (((32 + 2 * ((CONVERT(INT, @IsHollidayYear / 100)) % 4) + 2 * (CONVERT(INT, (@IsHollidayYear % 100) / 4)) – (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) – ((@IsHollidayYear % 100) % 4)) % 7)) – 7 * (CONVERT(INT, ((@IsHollidayYear % 19) + 11 * (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + 22 * (((32 + 2 * ((CONVERT(INT, @IsHollidayYear / 100)) % 4) + 2 * (CONVERT(INT, (@IsHollidayYear % 100) / 4)) – (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) – ((@IsHollidayYear % 100) % 4)) % 7))) / 451)) + 114) % 31) + 1), 2))

    — Easter
    IF @IsHolliday = @IsHollidayEasterDate – 2 OR
    @IsHolliday = @IsHollidayEasterDate OR
    @IsHolliday = @IsHollidayEasterDate + 1 BEGIN
    RETURN 1
    END

    — Ascension Day
    IF @IsHolliday = DATEADD(dd, 39, @IsHollidayEasterDate) BEGIN
    RETURN 1
    END

    — Whitsun
    IF @IsHolliday = DATEADD(dd, 49, @IsHollidayEasterDate) OR
    (@IsHolliday = DATEADD(dd, 50, @IsHollidayEasterDate) AND @IsHollidayYear < 2005) BEGIN
    RETURN 1
    END

    — Midsummer
    IF @IsHolliday = DATEADD(dd, 7 – DATEPART(dw, CONVERT(CHAR(4), @IsHollidayYear) + '-06-20'), CONVERT(CHAR(4), @IsHollidayYear) + '-06-20') BEGIN
    RETURN 1
    END

    — Halloween
    IF @IsHolliday = DATEADD(dd, 7 – DATEPART(dw, CONVERT(CHAR(4), @IsHollidayYear) + '-10-31'), CONVERT(CHAR(4), @IsHollidayYear) + '-10-31') BEGIN
    RETURN 1
    END

    RETURN 0
    END
    GO

    Reply
  • it’s usefull..
    thanks for your code..

    :D

    Reply
  • Shantanu Agarwal
    October 28, 2010 1:35 pm

    Hi,

    I need help in A stored procedure.
    I have to calculate a parameter only on week days,
    For eg if i give input date to be 10th october(Sunday) and today is 28th october.

    I have to calculate these parameters for 5 weekdays..
    So if i give A date which is a sunday then it should automatically skip that day and should calculate from Monday to friday.

    Another eg. Suppose the input date is a wednesday, then calculate the parameters for wednesday, thursday, friday, moday and tuesday.

    Skipping the Saturday and sunday.

    Could you please suggest a way out of this.

    Reply

Leave a Reply

Menu