locked
Filter Record With Day & Month RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my table data

    Field1 Ranges
    A 16-Mar - 23-Mar
    B 24-Mar - 30-May
    C 01-Jun - 15-Jun

    1) If user pass any date between this ranges has to show desire out put for example if i pass 27-Mar 

    then it has to show B

    2) If user pass any date between this ranges has to show desire out put for example if i pass 19-Mar

    then it has to show A

    How to do so...

    Tuesday, June 16, 2020 1:05 PM

Answers

  • User475983607 posted

    Table fields dont have year only date and month

    Right, this is a duplicate post.  XuDong Peng provided a C# sample and you marked the answer as solved! At least, click the link so you know what you're refuting. 

    IMHO, the main problem is your SQL design is poor.  This has been explained across many threads but you continue to make these poor design choices.  The easiest fix is normalizing the table design.   In this case add 4 columns to hold month and day integer values.  Then you don't have to create complex code to fix the poor design.

    Below is SQL example but it breaks down if the table can contain multiple years.

    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 - 23-Mar'), 
    		('B', '24-Mar - 30-May'), 
    		('C', '01-Jun - 15-Jun')
    
    DECLARE @Filter DATETIME = CONVERT(DATETIME, '15-Jun-1900', 106)
    
    SELECT	Field1,
    		Ranges
    FROM #PoorTableDesign
    WHERE @Filter BETWEEN 
    	CONVERT(DATETIME, RTRIM(LEFT(Ranges, 6) + '-1900'), 106)
    	AND CONVERT(DATETIME, LTRIM(RIGHT(Ranges, 6) + '-1900'), 106)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 23, 2020 1:40 PM

All replies

  • User-807418713 posted

    Hello

    Thanks But This Time Data is in Ranges Without year example like below

    16-Mar - 23-Mar

    24-Mar - 30-May

    01-Jun - 15-Jun

    That Code Not Work Use..

    Thanking You

    Tuesday, June 16, 2020 3:05 PM
  • User475983607 posted

    This is a duplicate post.   You marked the previous post as answered; https://forums.asp.net/p/2168125/6308581.aspx?Filter+Without+Year

    Tuesday, June 16, 2020 3:22 PM
  • User-807418713 posted

    Hello

    Table fields dont have year only date and month

    his is my table data

    Field1 Ranges
    A 16-Mar - 23-Mar
    B 24-Mar - 30-May
    C 01-Jun - 15-Jun

    1) If user pass any date between this ranges has to show desire out put for example if i pass 27-Mar 

    then it has to show B

    Tuesday, June 23, 2020 12:40 PM
  • User475983607 posted

    Table fields dont have year only date and month

    Right, this is a duplicate post.  XuDong Peng provided a C# sample and you marked the answer as solved! At least, click the link so you know what you're refuting. 

    IMHO, the main problem is your SQL design is poor.  This has been explained across many threads but you continue to make these poor design choices.  The easiest fix is normalizing the table design.   In this case add 4 columns to hold month and day integer values.  Then you don't have to create complex code to fix the poor design.

    Below is SQL example but it breaks down if the table can contain multiple years.

    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 - 23-Mar'), 
    		('B', '24-Mar - 30-May'), 
    		('C', '01-Jun - 15-Jun')
    
    DECLARE @Filter DATETIME = CONVERT(DATETIME, '15-Jun-1900', 106)
    
    SELECT	Field1,
    		Ranges
    FROM #PoorTableDesign
    WHERE @Filter BETWEEN 
    	CONVERT(DATETIME, RTRIM(LEFT(Ranges, 6) + '-1900'), 106)
    	AND CONVERT(DATETIME, LTRIM(RIGHT(Ranges, 6) + '-1900'), 106)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 23, 2020 1:40 PM