CREATE TABLE [dbo].[bankholidays](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[holiday] [nvarchar](150) COLLATE Latin1_General_CI_AS NULL,
	[holidaydate] [datetime] NULL
) ON [PRIMARY]

if exists(select * from bankholidays where holiday=@date)

	select min(dates) as next_date from
	(
	select dateadd(day,number,@date) as dates from master..spt_values where type='p' and number>=0
	) as t1
	left join bankholidays as t2
	on t1.dates=t2.holiday
	where t2.holiday is null

else
	select @date as next_date

quote:

---

Originally posted by madhivanan

Try something like this

if exists(select * from bankholidays where holidaydate=@date)

	select min(dates) as next_date from
	(
	select dateadd(day,number,@date) as dates from master..spt_values where type='p' and number>=0
	) as t1
	left join bankholidays as t2
	on t1.dates=t2.holidaydate
	where t2.holiday is null

else
	select @date as next_date

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by esthera

thanks i'm getting Conversion failed when converting datetime from character string.

---

quote:

---

Originally posted by vaibhavktiwari83

quote:

---

Originally posted by madhivanan

Try something like this

if exists(select * from bankholidays where holidaydate=@date)

	select min(dates) as next_date from
	(
	select dateadd(day,number,@date) as dates from master..spt_values where type='p' and number>=0
	) as t1
	left join bankholidays as t2
	on t1.dates=t2.holidaydate
	where t2.holiday is null

else
	select @date as next_date

Madhivanan

Failing to plan is Planning to fail

---

little typo error in red

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

---

CREATE TABLE Holidays (Holiday_Dt datetime, HoldayName varchar(255))
GO

INSERT INTO Holidays(Holiday_Dt, HoldayName)
SELECT '1/1/2010', 'New Years Day' UNION ALL
SELECT '1/18/2010', 'Martin Luther King' UNION ALL
SELECT '2/15/2010', 'President''s Day' UNION ALL
SELECT '5/31/2010', 'Memorial Day' UNION ALL
SELECT '7/5/2010', 'July 4th Holiday' UNION ALL
SELECT '9/6/2010', 'Labor Day' UNION ALL
SELECT '11/25/2010', 'Thanksgiving' UNION ALL
SELECT '11/26/2010', 'Black Friday' UNION ALL
SELECT '12/24/2010', 'Christmas Holiday' UNION ALL
SELECT '12/31/2010', 'New Years Eve'
GO

DECLARE @inp_Dt datetime; SET @inp_Dt = '11/25/2010'

    SELECT TOP 1 CASE WHEN Holiday_Dt IS NULL THEN inp_Dt ELSE DATEADD(d,n,inp_Dt) END AS Next_Available_Dt
      FROM (SELECT @inp_Dt AS inp_Dt) AS XXX 
 LEFT JOIN Holidays h
		ON  h.Holiday_Dt = xxx.inp_Dt
 LEFT JOIN (SELECT 1 n UNION SELECT 2 n UNION SELECT 3 n UNION SELECT 4 n UNION 
			SELECT 5 n UNION SELECT 6 n UNION SELECT 7 n) AS n
		ON  xxx.inp_Dt < DATEADD(d,n,inp_Dt)
     WHERE DATENAME(dw,DATEADD(d,n,inp_Dt)) NOT IN ('Saturday','Sunday')
	   AND NOT EXISTS (SELECT * FROM Holidays WHERE Holiday_Dt = DATEADD(d,n,inp_Dt))
GO

DROP TABLE Holidays
GO