Login     Register

        Contact Us     Search

XLeratorDB/strings Online Documentation

SQL Server WORKDAYS function


WORKDAY_q
 
Updated: 15 February 2010

Use WORKDAY_q to calculate the date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. The weekend days are always assumed to be Saturday and Sunday.
Syntax
SELECT [wctString].[wct].[WORKDAY_q] (
  <@StartDate, datetime,>
 ,<@Days, int,>
 ,<@HolidayDates_RangeQuery, nvarchar(max),>)
Arguments
@StartDate
               
The starting date for the calculation. Must be of data types that are implicitly convertible to datetime.
 
@Days
               
is the number of non-weekend and non-holiday days before or after the @StartDate. A positive value for @Days yields a future date; a negative value yields a date in the past.
 
@HolidayDates_RangeQuery
the select statement, as text, which will return a resultant table containing the holiday dates. The resultant table can only contain values of a datetime date type or that will implicitly convert to datetime.
Return Types
float
Remarks
·         For more simpler queries, use WORKDAY.
·         The calendar may be omitted by entering '' in the @Holiday_TableName.
·         Saturday and Sunday are assumed to be non-working days.
·         If @Days is not an integer, it is truncated.
Examples
In this example, we want to calculate the next workday after 12/31/2009.

SELECT wct.WORKDAY_q(
      '12/31/2009'
      ,1
      ,REPLACE('SELECT ''1/1/2010''
      ''1/18/2010''
      ''2/15/2010''
      ''4/2/2010''
      ''5/31/2010''
      ''7/5/2010''
      ''9/6/2010''
      ''11/25/2010''
      ''12/24/2010''', CHAR(13) + CHAR(10), ' UNION ALL' + CHAR(10) + 'SELECT ')
      )

This produces the following result.
-----------------------
2010-01-04 00:00:00.000
 
(1 row(s) affected)

If you did not wish to specify a holiday calendar, it can be omitted from the calculation by entering blank ('') in @Holiday_TableName and @HolidayDates_ColumnName.
SELECT wct.WORKDAY_q(
      '12/31/2009'
      ,1
      ,''
      )

This produces the following result.
-----------------------
2010-01-01 00:00:00.000
 
(1 row(s) affected)

If the holidays are stored in a table,
CREATE TABLE #t (
      Holiday     datetime
)
INSERT INTO #t
SELECT '1/1/2010' UNION ALL
SELECT '1/18/2010' UNION ALL
SELECT '2/15/2010' UNION ALL
SELECT '4/2/2010' UNION ALL
SELECT '5/31/2010' UNION ALL
SELECT '7/5/2010' UNION ALL
SELECT '9/6/2010' UNION ALL
SELECT '11/25/2010' UNION ALL
SELECT '12/24/2010'
 
SELECT wct.WORKDAY_q(
      '12/31/2009'
      ,30
      ,'SELECT * FROM #t'
      )

If @Days is a negative number, the result will be less than the start date
CREATE TABLE #t (
      Holiday     datetime
)
INSERT INTO #t
SELECT '1/1/2010' UNION ALL
SELECT '1/18/2010' UNION ALL
SELECT '2/15/2010' UNION ALL
SELECT '4/2/2010' UNION ALL
SELECT '5/31/2010' UNION ALL
SELECT '7/5/2010' UNION ALL
SELECT '9/6/2010' UNION ALL
SELECT '11/25/2010' UNION ALL
SELECT '12/24/2010'
 
SELECT wct.WORKDAY_q(
      '2/16/2010'
      ,-30
      ,'SELECT * FROM #t'
      )

This produces the following result.
-----------------------
2009-12-31 00:00:00.000
 

(1 row(s) affected)



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service