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

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)

About these ads

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

  1. 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

  2. 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

  3. 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

  4. @RAHUL

    You mean this ??

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

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

  5. 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?

    • 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

  6. 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)

  7. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  8. select DATEDIFF ( day , ’02/13/2009′ , ’02/12/2009′ )
    select DATEDIFF ( hour , ’02/13/2009′ , ’02/12/2009′ )

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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.

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s