# Check if Feb-29 is falling between start and end dates

• ### 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:

Regards,

Ram.

Monday, July 18, 2016 4:22 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

### 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 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 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 Tuesday, July 19, 2016 6:00 AM
Tuesday, July 19, 2016 5:53 AM