locked
Check if Feb-29 is falling between start and end dates RRS feed

  • Question

  • Hi Team,

    I want to determine if Feb-29 is falling between start and end dates for a leap year.

    Input Ex:

    DECLARE @TABLE TABLE
    (
    	START_DATE DATETIME,
    	END_DATE DATETIME
    )
    INSERT INTO @TABLE
    SELECT '2015-01-01','2015-01-31'
    UNION ALL
    SELECT '2015-02-01','2015-03-05'
    UNION ALL
    SELECT '2016-01-01','2016-01-31'
    UNION ALL
    SELECT '2016-02-01','2016-03-05'
    UNION ALL
    SELECT '2015-01-01','2016-03-04'
    
    SELECT *FROM @TABLE

    Expected output:

    Appreciate your inputs.

    Regards,

    Ram.


    Monday, July 18, 2016 4:22 PM

Answers

  • Easiest is to create a calendar table (google if you don't know they are, but basically it is just a table with every date between some some starting date and an ending date, and maybe some other helpful columns like whether the day is a holiday in your company).  Then the query is just (assuming dt is the column in your calendar table containing the date)

    Select START_DATE, END_DATE, 
      Case When Exists(Select * From dbo.Calendar Where dt Between START_DATE And END_DATE And Month(dt) = 2 And Day(dt) = 29) Then 1
        Else 0
      End As Check1
    From @TABLE
    Tom
    Monday, July 18, 2016 5:32 PM

All replies

  • SELECT 
    START_DATE ,
    end_date ,
    case when isdate((CAST(year([START_DATE]) as char(4)) + '-02-29')) = '1'  Then
    	case when (CAST(year([START_DATE]) as char(4)) + '-02-29') between START_DATE  and end_date  then '1' 
    		Else '0'
    	End 
    Else
    	case when isdate((CAST(year(end_date) as char(4)) + '-02-29')) = '1'  Then
    		case when (CAST(year([end_date]) as char(4)) + '-02-29') between START_DATE  and end_date  then '1' 
    			Else '0'
    		End 
    	Else '0' 
    	End
    End 'Check'
     
    FROM @TABLE




    • Edited by Venkat786 Monday, July 18, 2016 6:10 PM
    Monday, July 18, 2016 5:01 PM
  • 2015 is not a leap year, so I'm skeptical about your question and the underlying assumption.  What are you actually trying to accomplish? Is this related to your prior question about getting all monthly start and end dates?  Because it still seems like you do not yet understand the power of a calendar table - regardless of whether it is stored or dynamically generated.

    And can you just skip the pointless "signature" about marking your post as an answer - especially when you are posting a question?

    • Proposed as answer by Naomi N Monday, July 18, 2016 10:30 PM
    Monday, July 18, 2016 5:09 PM
  • Thanks, I have tried similar solution but it is becoming too complicated which can be made simple I guess. And the query is not working for all scenario in my input EX last row should be 1. Regards, Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Monday, July 18, 2016 5:15 PM
  • Ok if it is a leap year and the Feb-29 is in between start and end date then check1 should be 1 else it should 0.

    Hope that clarifies. That signature is coming by default dude... :) yeah agree it doesn't make any sense when i am posting a question but it doesn't option to only post in case i reply to a question sorry.

    Regards,

    Ram.




    Monday, July 18, 2016 5:18 PM
  • Simple solution then this?

    SELECT 
    START_DATE ,
    end_date ,
    CASE WHEN (ISDATE((CAST(year([START_DATE]) as char(4)) + '-02-29')) = '1' OR ISDATE((CAST(year([END_DATE]) as char(4)) + '-02-29')) = 1) THEN
    	CASE WHEN (ISDATE((CAST(year([START_DATE]) as char(4)) + '-02-29')) = '1' AND (CAST(year([START_DATE]) as char(4)) + '-02-29') between START_DATE  and end_date) THEN	
    			'1'
    		WHEN (ISDATE((CAST(year([END_DATE]) as char(4)) + '-02-29')) = 1 AND (CAST(year([END_DATE]) as char(4)) + '-02-29') between START_DATE  and end_date) THEN
    			'1'
    	ELSE '0'
    	END
    ELSE
    	'0'
    End Check1
    FROM @TABLE

    Regards,

    Eshwar.

    Monday, July 18, 2016 5:30 PM
  • Easiest is to create a calendar table (google if you don't know they are, but basically it is just a table with every date between some some starting date and an ending date, and maybe some other helpful columns like whether the day is a holiday in your company).  Then the query is just (assuming dt is the column in your calendar table containing the date)

    Select START_DATE, END_DATE, 
      Case When Exists(Select * From dbo.Calendar Where dt Between START_DATE And END_DATE And Month(dt) = 2 And Day(dt) = 29) Then 1
        Else 0
      End As Check1
    From @TABLE
    Tom
    Monday, July 18, 2016 5:32 PM
  • Using your favorite calendar table, you could simply:

    CASE WHEN EXISTS (SELECT today FROM dbo.calendar WHERE month = 2 AND day = 29 AND YEAR = a.year) THEN 1 ELSE 0 END


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, July 18, 2016 5:35 PM
  • But you still haven't identified your actual goal.  Generally speaking, there isn't much utility to knowing if Feb 29 of any random year not only exists but is also between two other random dates. So how do you intend to use this information?  Because it is that "usage" that will likely determine the best path forward.

    But your problem can be simplified without the Feb 29th problem. We know that March 1 exists in every year. So we only need to know if start date < March 1 or if end date >= March 1 and that either year of start date or end date is a leap year. And this is where you can take a shortcut. The logic for determining if any given year is a leap year is well known - it is relatively simple and you should be able to code that in tsql. With this logic you never need to worry about errors occurring when you attempt to compare your values against march 1 of the same year.  And for your own well-being, read Tibor's discussion of datetime datatype. You have chosen a literal format that is not always safe.

    tibor - datetime guide

    Or really - just add the "leap year" fact to a table.

    Monday, July 18, 2016 5:50 PM
  • Try this:

    DECLARE @TABLE TABLE
    (
    	START_DATE DATETIME,
    	END_DATE DATETIME
    )
    INSERT INTO @TABLE
    SELECT '2015-01-01','2015-01-31'
    UNION ALL
    SELECT '2015-02-01','2015-03-05'
    UNION ALL
    SELECT '2016-01-01','2016-01-31'
    UNION ALL
    SELECT '2016-02-01','2016-03-05'
    UNION ALL
    SELECT '2016-01-01','2016-03-04'
    
    SELECT *
        ,CASE WHEN DATEADD(DAY,-1,CAST(CAST(YEAR(END_DATE) AS VARCHAR(4)) + '-03-01' AS DATE)) BETWEEN [START_DATE] AND [END_DATE] THEN 
    	   CASE WHEN DAY(DATEADD(DAY,-1,CAST(CAST(YEAR(END_DATE) AS VARCHAR(4)) + '-03-01' AS DATE))) = 29 THEN 1 ELSE 0  END ELSE 0 END AS LeapDay
    FROM @TABLE
    

    Monday, July 18, 2016 7:54 PM
  • Hi,

    The fact that the requested date is February 29th is not relevant, SQL validates dates so you'll never have a wrong value like 2015-02-29.

    Just run the following query

    SET @d DATE = CAST('2016-02-29' AS DATE)

    IF EXISTS(SELECT * FROM @TABLE WHERE @d BETWEEN START_DATE AND END_DATE) 

    PRINT 'Yes'

    ELSE

    PRINT 'No'



    Monday, July 18, 2016 8:13 PM
  • -- Can you try this 
    
    --SQL
    
    ;WITH  SQL_Dates_Feb AS 
    (
      SELECT  MIN(YEAR([START_DATE])) AS [yyyy] ,MAX(YEAR([END_DATE])) AS [yyyy_max] , EOMONTH( CONCAT(  MIN(YEAR([START_DATE]))  , '-FEB-01'  ) )    AS  Feb_SQLDate  FROM @TABLE   
      UNION ALL
      SELECT  [yyyy] +1 , [yyyy_max]   ,  EOMONTH(  CONCAT(   [yyyy] +1  , '-FEB-01'  ) )     AS  Feb_SQLDate
      FROM SQL_Dates_Feb WHERE  [yyyy] +1 <=[yyyy_max]
    
    )  
     , Only_Feb_29 AS 
     (
      SELECT * FROM  SQL_Dates_Feb   WHERE DATEPART( dd, Feb_SQLDate ) = 29
     ) 
    
    SELECT t.*   , 
     IIF((SELECT MAX(Feb_SQLDate)  FROM Only_Feb_29 WHERE   Feb_SQLDate BETWEEN  t.[START_DATE] AND t.[END_DATE] ) IS NULL , 0 , 1) AS Check1
    FROM @TABLE t  
    
    
    


    rajivkumar.bala@yahoo.co.in

    Monday, July 18, 2016 10:18 PM
  • This should work for any date range

    ;
    with feb as
    (select year(case when min(START_DATE)>min(END_DATE)then min(END_DATE) else min(START_DATE) end) fdt from@TABLE
    union all
    select year(casewhenmax(START_DATE)>max(END_DATE) then max(END_DATE)else max(START_DATE) end) fdt
    from@TABLE)
    select *,case when fdt between start_date and end_date then 1
    else 0
    end as check1
    from @TABLE
    outer apply(select cast(formatmessage('%d%s',fdt,'0229')as date) fdt
    from feb where
    isdate(formatmessage('%d%s',fdt,'0229'))=1)a

    
    


    • Edited by abdulnazar Tuesday, July 19, 2016 6:00 AM
    Tuesday, July 19, 2016 5:53 AM