Select all records if it is a leap year else exclude 29 february

Question

I am trying to exclude people with birthdays on 29.02 this year, because this year is not a leap year and the following query is working. But when I set the year to 2024, it is not giving me any results. I want to select all records if a year is leap and have a where clause if a year is not.

How can I have if clause in where statement?

DECLARE @year int
set @year = YEAR(GETDATE()) 
--set @year = 2024

SELECT * FROM Person
WHERE ISDATE(CAST(@YEAR AS char(4)) + '0229') != 1 AND MONTH(DateOfBirth) != 2 AND DAY(DateOfBirth)!= 29

Don't use IF in a WHERE clause. Stick to regular AND/OR, and everything will be fine. — jarlh, Feb 27 '23 at 15:25
People who have a birthday on 29/02 still have birthdays in non-leap years, so excluding them seems "wrong". — Thom A, Feb 27 '23 at 15:25
@jarlh - `ISDATE('20240229')` returns `1` and `ISDATE('20230229')` returns `0` — Martin Smith, Feb 27 '23 at 15:28
https://stackoverflow.com/questions/725098/leap-year-calculation gives you the formula to include in your where clause. every 4 years except when it's divisible by 100 unless it's divisible by 400. so year 4,8,12... but not 100, but include 400. so case when .... then include 2/29. else don't. — xQbert, Feb 27 '23 at 15:30
The statement "*Exclude (is not a leap year AND birthdate is Feb 29)*" is equivalent to "*Include (is a leap year OR birthdate is not Feb 29)*", which is equivalent to "*Include (is a leap year OR birth month is not Feb OR birth day-of-month is not 29)*", which translates to `WHERE (ISDATE(CAST(@YEAR AS char(4)) + '0229') = 1 OR MONTH(DateOfBirth) != 2 OR DAY(DateOfBirth) != 29)`. — T N, Feb 27 '23 at 15:36
What is it you are *actually* trying to do here, work out what someone's birthday would be this year? — Thom A, Feb 27 '23 at 15:36
Thank you @TN. Your solution is working. Please post it as an answer so I can accept it — Enchantres, Feb 27 '23 at 15:45
@Larnu I am displaying on the frontend list of users that have birthdays today, tomorrow and upcoming next week and I came into an error that 29 feb cannot be included because this date doesn't exist on a non-leap-year, so for a non-leap-year I will have one more list where I will display the birthdays on that date in order to let the users wish their birthdays. I decided to create it as a separate query — Enchantres, Feb 27 '23 at 16:13
Why not work out what their birthday would be this year, and then if it's in the next -1 to +7 days? — Thom A, Feb 27 '23 at 16:15
I tried this but I am getting *Cannot construct data type date, some of the arguments have values which are not valid* because of this line ***datepart(dayofyear, Person.DateOfBirth)-datepart(dayofyear, getdate()) NumberOfDays, DATEDIFF(DAY, GETDATE(), DATEFROMPARTS(YEAR(GETDATE()), MONTH(DateOfBirth), DAY(DateOfBirth))) AS Datediff*** — Enchantres, Feb 27 '23 at 16:20
`I will have different logic for them` -> if this is not "on a leap year we'll use Feb 28th" are you sure all of your users will be happy with your logic? — Aaron Bertrand, Feb 27 '23 at 16:49

Thom A · Answer 1 · 2023-02-28T16:08:58.200

I'm answering the X of what I feel is an XY Problem here; the OP doesn't need logic to exclude people with a birthday of 29 February, they need a way to work out what their birthday is for a specific (this) year, regardless of if it's a leap year.

This can be quite simple; just add the number of years that have passed since their birthday. SQL Server will automatically handle leap years, so 2004-02-29 plus 19 years would result in 2023-02-28.

So, if you want people who have a birthday in the next week:

WITH Birthdays AS(
    SELECT BD.BirthDate,
           DATEADD(YEAR, YEAR(GETDATE()) - YEAR(BD.BirthDate),BD.BirthDate) AS BirthDay --This year's birthday
    FROM (VALUES(CONVERT(date,'19840405')),
                (CONVERT(date,'19961031')),
                (CONVERT(date,'20040229')))BD(BirthDate))
SELECT *
FROM Birthdays
WHERE BirthDay >= CONVERT(date,GETDATE())
  AND BirthDay < CONVERT(date, DATEADD(DAY, 7, GETDATE()));

Note that different countries do have different rules on when someone ages. In the UK (which is where I live) they aren't actually classed as legally aging until 01 March, however, in other countries it's 28 February. If you wanted it to be 01 March, then you could use an additional CTE to check if the DAY of the 2 dates is the same; if it isn't then add a day to the BirthDay to make it 01 March (rather than 28 February):

WITH Birthdays AS(
    SELECT BD.BirthDate,
           DATEADD(YEAR, YEAR(GETDATE()) - YEAR(BD.BirthDate),BD.BirthDate) AS BirthDay --This year's birthday
    FROM (VALUES(CONVERT(date,'19840405')),
                (CONVERT(date,'19961031')),
                (CONVERT(date,'20040229')))BD(BirthDate)),
AdjustedBirthdays AS(
    SELECT BirthDate,
           CASE DAY(Birthdate) WHEN DAY(Birthday) THEN Birthday ELSE DATEADD(DAY,1,Birthday) END AS BirthDay
    FROM Birthdays)
SELECT *
FROM AdjustedBirthdays
WHERE BirthDay >= CONVERT(date,GETDATE())
  AND BirthDay < CONVERT(date, DATEADD(DAY, 7, GETDATE()));

T N · Accepted Answer · 2023-02-27T15:56:28.840

The statement "Exclude (is not a leap year AND birthdate is Feb 29)" is equivalent to "Include (is a leap year OR birthdate is not Feb 29)", which is equivalent to "Include (is a leap year OR birth month is not Feb OR birth day-of-month is not 29)", which translates to:

-- Only include Feb 29 birthdates if a leap year
WHERE (ISDATE(CAST(@YEAR AS char(4)) + '0229') = 1
       OR MONTH(DateOfBirth) != 2
       OR DAY(DateOfBirth) != 29)

This might actually be more readable with the "not" factored back out.

-- If not a leap year, exclude Feb 29 birthdates
WHERE NOT (ISDATE(CAST(@YEAR AS char(4)) + '0229') = 0
           AND MONTH(DateOfBirth) = 2
           AND DAY(DateOfBirth) = 29)

Per Larnu's comment, you might want to consider alternate handling of the non-lead-year Feb 29 case - Perhaps wish them a happy birthday on Feb 28 or March 1.

should one subtract 1 if their birthday is on feb29th on a non-leap year or add one? so it becomes a valid date? ahh leap day birthdays... — xQbert, Feb 27 '23 at 16:38

Select all records if it is a leap year else exclude 29 february

2 Answers2