Dates function for Reporting Services

Excel has a function called workday which takes out weekends and holidays and give you the date of when the project will be done
I'm can't find any function that does what workday function does in Reporting services.

DateAdd includes the weekends and holiday which i don't need.
I need something that can do something close to workday in excel

There is no such function that I am aware of. You can obviously approximate it by

@.WANTED = DATEADD(Day, 1.4 * @.WorkDays. @.StartDate)

To go beyond that requires a table of Holidays and a more involved function. There may be such a function i SQLCENTRAL.COM.

|||

Use

CREATE FUNCTION dbo.fnAddWorkdays(@.FROM DATETIME, @.DAYS INT)
-- Purpose:
-- Calculate Date @.DAYS in future from @.FROM
-- Copyright (C) 2007 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
RETURNS DATETIME AS
BEGIN
DECLARE @.YYYYMMDD CHAR(8)
WHILE @.DAYS > 0
BEGIN
SET @.FROM = DATEADD(day, 1, @.FROM)
SET @.YYYYMMDD = SUBSTRING(REPLACE(CONVERT(CHAR(20), @.FROM, 126), '-', ''), 1, 8)
IF DATEPART(weekday,@.FROM) NOT IN (1, 7) BEGIN
IF NOT EXISTS(SELECT * FROM Holiday WHERE YYYYMMDD = @.YYYYMMDD) SET @.DAYS = @.DAYS - 1
END
END
RETURN @.FROM
END
GO
SELECT dbo.fnAddWorkdays(GETDATE(), 1)
SELECT dbo.fnAddWorkdays(GETDATE(), 2)
SELECT dbo.fnAddWorkdays(GETDATE(), 3)

|||

Table create is

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Holiday]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Holiday] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[YYYYMMDD] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[Holiday] WITH NOCHECK ADD
CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Holiday] ON [dbo].[Holiday]([YYYYMMDD]) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'Date in yyyyMMdd notation', N'user', N'dbo', N'table', N'Holiday', N'column', N'YYYYMMDD'

GO