User475983607 posted
Gopi.MCA
Hello
This is my table data
|
Field1 |
Ranges |
|
A |
16-Mar-2020 - 23-Mar-2020 |
|
B |
24-Mar-2020 - 30-May-2020 |
|
C |
01-Jun-2020 - 15-Jun-2020 |
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.
