CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Arthur
Arthur

Reputation: 3473

How to select records which have date range with leap years?

I have table with struct like 

ID | Name | StartDate | EndDate

How to select records which have date range, which contains leap years? For example, records, which have date range from 2011-01-01 (StartDate) to 2013-01-01 (EndDate)? This range contains leap years. I use MS SQL.

Thank you.

Upvotes: 0

Views: 984

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460238

You could create a table LeapYears, then the query is simple as:

SELECT t.*
FROM TableName t
WHERE EXISTS
(
   SELECT 1 FROM LeapYears ly
   WHERE ly.[Date] >= StartDate AND ly.[Date] <= EndDate
)

To create a LeapYear table you could use this:

DECLARE @startYear int 
DECLARE @endYear int
SET @startYear = 2000
SET @endYear = 2030
CREATE  TABLE LeapYears(ID int IDENTITY(1,1), [Date] DATETIME)

DECLARE @currentYear int
SET @currentYear = @startYear
WHILE @currentYear <= @endYear
BEGIN
  DECLARE @yearStr char(4)
  SELECT @yearStr = CAST(@currentYear AS char(4))
  IF ISDATE(@yearStr + '0229') = 1  
     INSERT INTO LeapYears VALUES(@yearStr + '0229')
  SET @currentYear = @currentYear + 1
END

The script creates these records:

ID  Date
1   2000-02-29 00:00:00.000
2   2004-02-29 00:00:00.000
3   2008-02-29 00:00:00.000
4   2012-02-29 00:00:00.000
5   2016-02-29 00:00:00.000
6   2020-02-29 00:00:00.000
7   2024-02-29 00:00:00.000
8   2028-02-29 00:00:00.000

Upvotes: 2

Ricardo Silva
Ricardo Silva

Reputation: 1384

I suggest to you to write one stored procedure that receives the two datetime and verify if each year of period is leap year. To do this you can simple do year mod 4 = 0.

Upvotes: 0

Related Questions