If user pass any date between this ranges has to show desire out put for example if i pass 27-Mar-2020
then it has to show B
How to do so...
Geez Gopi.MCA, you share a lot of very poor table designs that require string manipulation. I'm surprised you struggle with this type of problem considering how many times you've asked similar questions. As illustrated in your other posts, use standard
T-SQL string functions to extract the two dates from the non-normalized table.
IF OBJECT_ID('tempdb..#PoorTableDesign') IS NOT NULL
DROP TABLE #PoorTableDesign
CREATE TABLE #PoorTableDesign (Field1 VARCHAR(1), Ranges VARCHAR(64))
INSERT INTO #PoorTableDesign (Field1, Ranges)
VALUES ('A', '16-Mar-2020 - 23-Mar-2020'),
('B', '24-Mar-2020 - 30-May-2020'),
('C', '01-Jun-2020 - 15-Jun-2020')
DECLARE @Filter DATETIME = CONVERT(DATETIME, '27-Mar-2020', 106)
SELECT Field1,
Ranges
FROM #PoorTableDesign
WHERE @Filter BETWEEN
CONVERT(DATETIME, RTRIM(LEFT(Ranges, CHARINDEX(' - ', Ranges))), 106)
AND CONVERT(DATETIME, LTRIM(RIGHT(Ranges, LEN(Ranges) - CHARINDEX(' - ', Ranges)-2)), 106)
Or
SELECT Field1,
Ranges
FROM #PoorTableDesign
WHERE @Filter BETWEEN
CONVERT(DATETIME, RTRIM(LEFT(Ranges, 11)), 106)
AND CONVERT(DATETIME, LTRIM(RIGHT(Ranges, 11)), 106)
Also, please notice that I created enough sample data to populate a temp table. Please do the same when asking for assistance. IMHO, it is a bit rude on your side expecting the community to write your code. The least you can do is setup the test data.
Member
236 Points
547 Posts
Filter Data Based On Given Date Input
Jun 13, 2020 10:45 AM|Gopi.MCA|LINK
Hello
This is my table data
If user pass any date between this ranges has to show desire out put for example if i pass 27-Mar-2020
then it has to show B
How to do so...
All-Star
48421 Points
21296 Posts
Re: Filter Data Based On Given Date Input
Jun 13, 2020 11:42 AM|mgebhard|LINK
Geez Gopi.MCA, you share a lot of very poor table designs that require string manipulation. I'm surprised you struggle with this type of problem considering how many times you've asked similar questions. As illustrated in your other posts, use standard T-SQL string functions to extract the two dates from the non-normalized table.
IF OBJECT_ID('tempdb..#PoorTableDesign') IS NOT NULL DROP TABLE #PoorTableDesign CREATE TABLE #PoorTableDesign (Field1 VARCHAR(1), Ranges VARCHAR(64)) INSERT INTO #PoorTableDesign (Field1, Ranges) VALUES ('A', '16-Mar-2020 - 23-Mar-2020'), ('B', '24-Mar-2020 - 30-May-2020'), ('C', '01-Jun-2020 - 15-Jun-2020') DECLARE @Filter DATETIME = CONVERT(DATETIME, '27-Mar-2020', 106) SELECT Field1, Ranges FROM #PoorTableDesign WHERE @Filter BETWEEN CONVERT(DATETIME, RTRIM(LEFT(Ranges, CHARINDEX(' - ', Ranges))), 106) AND CONVERT(DATETIME, LTRIM(RIGHT(Ranges, LEN(Ranges) - CHARINDEX(' - ', Ranges)-2)), 106)Or
Also, please notice that I created enough sample data to populate a temp table. Please do the same when asking for assistance. IMHO, it is a bit rude on your side expecting the community to write your code. The least you can do is setup the test data.
Member
236 Points
547 Posts
Re: Filter Data Based On Given Date Input
Jun 14, 2020 02:44 PM|Gopi.MCA|LINK
Thank You So Much @ mgebhard