Hi G.Madri,
Refer the below sample query.
SQL
CREATE TABLE Demo
(
FromDate DATETIME,
ToDate DATETIME,
Dates VARCHAR(MAX)
)
GO
--------------------------------------------------------------------
INSERT INTO Demo VALUES('2016-10-19','2016-10-19','''19-10-2016''')
INSERT INTO Demo VALUES('2017-03-08','2017-03-11','''08-03-2017'',''09-03-2017'',''10-03-2017'',''11-03-2017''')
GO
--------------------------------------------------------------------
-- The SplitString function is a Table-Valued function that returns a Table as output and accepts two parameters
-- 1. @Input – The string to be split.
-- 2. @Character – The character that is used to separate the words or letters.
CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
--------------------------------------------------------------------
DECLARE @Date VARCHAR(10)
SET @Date = '07-03-2017'
IF EXISTS(SELECT Item
FROM dbo.SplitString(
(SELECT SUBSTRING(Dates,1,LEN(Dates)-1) Dates
FROM(
SELECT DISTINCT(
SELECT REPLACE(Dates,'''','') + ','
FROM Demo
ORDER BY Dates
FOR XML PATH('')
) AS Dates
FROM Demo
)Result)
, ',')
WHERE Item = @Date)
BEGIN
SELECT @Date + ' is within that date list' AS Result
END
ELSE
BEGIN
SELECT @Date + ' is not within that date list' AS Result
END
GO
--------------------------------------------------------------------
Output
For date 08-03-2017 : 08-03-2017 is within that date list
For date 07-03-2017 : 07-03-2017 is not within that date list
For more details about splitstring refer the below article.