Share via

T-SQL: Adding Work Days

  • Article

Introduction

This article presents a Transact-SQL Scalar Function to determine a future date, only considering the next working days.

Determine a future working day is a very common process in service delivery companies such as: Transporters, production of perishable food and other.

This sample helps those who have a similar need, as described above. You can add more details to enhance the accuracy if needed, specifying hours, minutes and seconds.

Please feel free to add more useful content.

Defining work days

Depending on the different cultures in each Country or even each Job sectors, we can have different start days of the week.

We can better understand this configuration and also as SQL Server work day of the week (by default), showing in  the calendar below the "work day" used to demo (Wednesday, 08/27/2014) and "off day work" (Saturday, 08/30/2014 and Sunday, 08/31/2014).

See below the image.

By default, the SQL Server define Sunday (weekday=7) as the start of the week based on "us_english" language. This information we can get through @@DATEFIRST variable, but it's important to note that day indicated in SET DATEFIRST configuration is only a reference for "weekday" parameter. This is used and better explained in the BOL - DATEPART method.

In T-SQL script below, we change "start of the week" reference(weekday) just changing the language in the current context. This demo, vamos compares "Brazilian Portuguese", "British English" and "American English"(default) languages, using date "08/31/2014" (Sunday).

See below the image (click to enlarge).

See below this T-SQL script

DECLARE @SampleDate AS DATE = '2014-08-31';

SET LANGUAGE Brazilian;             -- Brazilian

SELECT 'Brazilian' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,

DATENAME(weekday,@SampleDate) AS WeekDayName,DATEPART(weekday,@SampleDate) AS WeekDayNumber;

SET LANGUAGE British;                -- English - United Kingdom

SELECT 'English -UK' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,

DATENAME(weekday,@SampleDate) AS WeekDayName,DATEPART(weekday,@SampleDate) AS WeekDayNumber;

SET LANGUAGE us_english;             -- English - United States

SELECT 'English - USA' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,

DATENAME(weekday,@SampleDate) AS WeekDayName,DATEPART(weekday,@SampleDate) AS WeekDayNumber;

GO

We can see to change start of the weekday, based on cultural pattern for each Country, changed SET LANGUAGE configuration and therefore the "weekday" reference number for each day of a week.

Now if we use the SET DATEFIRST configuration, this command is higher than in the SQL Server behavior and their weekday relationship.

In this way, the T-SQL script below  modifies the SQL Server default and all queries that will have same identifier for "weekday".

See below the image (click to enlarge).

See below this T-SQL script

DECLARE @SampleDate AS DATE = '2014-08-31';

SET DATEFIRST 2;

SET LANGUAGE Brazilian;             -- Brazilian

SELECT 'Brazilian' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,

DATENAME(weekday,@SampleDate) AS WeekDayName, DATEPART(weekday,@SampleDate) AS WeekDayNumber;

SET LANGUAGE British;               -- English - United Kingdom

SELECT 'English - UK' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,

DATENAME(weekday,@SampleDate) AS WeekDayName, DATEPART(weekday,@SampleDate) AS WeekDayNumber;

SET LANGUAGE us_english;             -- English - United States

SELECT 'English - USA' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,

DATENAME(weekday,@SampleDate) AS WeekDayName, DATEPART(weekday,@SampleDate) AS WeekDayNumber;

GO

We can see to change the start day a week by SET DATEFIRST setting to use Job sectors becomes new "weekday" in the SQL Server context, regardless of the "language - Country" used.

In the following demo, we are using the SQL Server weekday default (us_english), as shown in the table below:

Day of Week  DATEFIRST  SQL WeekDay (default)
Monday 1 2
Tuesday 2 3
Wednesday 3 4
Thursday 4 5
Friday 5 6
Saturday 6 7
Sunday 7 1

Considering that the "Off Days" used for this demo are Saturday and Sunday, we will remove days respectively with "weekday" 7 and 1.

Creating the Function

The structure of this function intends to dispose non-working days for requested service to establish a delivery date.

Thereby, we use the methods: DATEADD and DATEPART to add and compare date scheduled for next working day, discarding "Saturday" (weekday = 7) and "Sunday" (weekday = 1).

You can also discard the national holidays, state(where applicable - for each country) holidays and/or city holidays, but although not the scope of this article we can add a query table to output several holidays and thus also removing these dates.

I recommend way of error handling, include a check for @DATE parameter. If the  value is NULL, then get current date using the GETDATE function.

Soon after, we make a loop using @COUNT variable as counter until the number of days indicated on @NDAYS parameter.

See below this T-SQL script

CREATE FUNCTION dbo.ufn_ADD_WORKING_DAYS (

  @DATE      DATE,

  @NDAYS     INT   

) RETURNS DATE     

BEGIN         

       IF @DATE IS NULL

         BEGIN       

           SET @DATE = GETDATE();

         END

       DECLARE @STARTDATE  INT  = 0

       DECLARE @COUNT      INT  = 0

       DECLARE @NEWDATE    DATE = DATEADD(DAY, 1, @DATE)                                         

       WHILE @COUNT < @NDAYS 

        BEGIN 

          IF DATEPART(WEEKDAY, @NEWDATE) NOT IN (7, 1) --AND @NEWDATE NOT IN ( SELECT DT_HOLIDAY FROM TB_HOLIDAYS ) 

            SET @COUNT += 1;

            SELECT @NEWDATE = DATEADD(DAY, 1, @NEWDATE), @STARTDATE += 1;

        END 

        RETURN DATEADD(DAY, @STARTDATE, @DATE);

  END 

GO

Note

The starting day of the week (using SET DATEFIRST) as we used in this demo is the SQL Server default.

You can change this Scalar Function on T-SQL script for dismiss other "days off" as used in the IN condition.

Using the Function

See below a sample. We have a string date, converted using CAST function to DATE datatype and we added 4 working days.

So, we use 08/27/2014 (Wednesday) and considering we were removing days: 08/30/2014 (Saturday) and 08/31/2014 (Sunday), then we will now have resulting 09/02/2014 as the next working day.

See below the image.

See below this T-SQL script.

SELECT dbo.ufn_ADD_WORKING_DAYS( CAST('2014-08-27' AS DATE), 4 )

GO

This function can also be used in a SELECT statement to data query table, using as a parameter in this Scalar Function a column with datatype DATE.

Conclusion

This Scalar Function is important to identify a date limit to work or delivery schedules, using only next working days to enhance conditions for data storage and display on reports. 

Propose a deadline for completion of work with higher accuracy is one of the best criteria to differentiate your Company on the market.

References

See Also

Other Languages