How to select date records from a table with leap and non-leap year?

Question

How can I create a sql-server query that ignores 29-of February in a non-leap year and include 29-of February in leap year. (Assuming I have a column in a table with dates from 2000-01-01 to 2016-12-31 for example)

At the moment I have this but it completely removes the leap years.

SELECT uid, CONVERT(DATE,CAST(DATEPART(yyyy,day) AS VARCHAR(4))+'-'+
                CAST([Month] AS VARCHAR(2))+'-'+
                CAST(29 AS VARCHAR(2))) AS code_date, [29] AS code  FROM [data]
                WHERE DATEPART(mm,day) <> 2  AND NOT (ISDATE(CAST(DATEPART(yyyy,day) AS char(4)) + '0229') = 1)

However, I only want to remove all 29th of February in every year except a leap year.

Please explain what you are trying to accomplish. The `date` data type doesn't support Feb 29th in non-leap years, so I can figure out what you really want to do. — Gordon Linoff, Oct 24 '16 at 18:01
The dates in my database are not clean and they include 28 and 29 for all leap and non-leap years. What I want is to select only the dates that includes all years but ignores 29,30&31 for non-leap year and 30&31 for leap year. — Tee, Oct 25 '16 at 08:23

score 0 · Answer 1 · edited May 23 '17 at 12:16

SQL Server knows what years are leap years :)

select isdate('2/29/2015')
select isdate('2/29/2016')

The return value of ISDATE depends on the settings set by SET DATEFORMAT, SET LANGUAGE and Configure the default language Server Configuration Option.

Here is a function to tell you if the year is a leap year:

CREATE FUNCTION dbo.IsLeapYear (@year INT)
RETURNS INT
AS
BEGIN
RETURN(IIF(DATEPART(dd,(EOMONTH(CONCAT(@year,'0201')))) = 29,1,0))
END
GO

You could just create a calendar table or use it in a CTE as noted HERE:

--notice 2/29/2016 but no 2/29/2015
DECLARE @MinDate DATE = '20150201',
        @MaxDate DATE = '20160306';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

Tee · Accepted Answer · 2016-10-25T08:53:07.810

0

I finally figure it out and here is the answer

SELECT uid, CONVERT(DATE,CAST(DATEPART(yyyy,mydate) AS VARCHAR(4))+'-'+
                CAST([Month] AS VARCHAR(2))+'-'+
                CAST(29 AS VARCHAR(2))) AS code_date, [29] AS code  FROM [data]
                WHERE DATEPART(mm,mydate) <> 2  OR (ISDATE(CAST(DATEPART(yyyy,mydate) AS char(4)) + '0229') = 1)

This selects only 29th of February if only the date is a leap year and excludes 29th of February in non-leap year.

edited Oct 25 '16 at 08:53

answered Oct 25 '16 at 08:50

Tee

385
3
14

Have you considered fixing the data in the database? Why do you have rows in the database that is by design wrong and must be ignored? – Lasse V. Karlsen Oct 25 '16 at 08:52
The data was imported into the database from another source (excel) and it includes incorrect dates – Tee Oct 25 '16 at 08:55

How to select date records from a table with leap and non-leap year?

2 Answers2