Birth year between 2 dates

Transact-SQL
1

I have a very tricky (for me) select statement that I don't know how to solve.
Using MS SQL server...
2 tables, one with competition results (results) and one with personal data (member).
Have memberid in both tables for my join

I want to get the number of starters between 2 dates in each class whose birth year (taken from the column born (member) e.g. 2012-05-12) and competition style is sled

For example. Number of starters who have a birth year of 2012 between the dates 2025-01-01 to 2025-12-31

I've gotten this far, but I don't know how to filter birth year (only the year) in the select statement.

SELECT r.Klass,
count(r.Sluttid) + count(Case When r.NF = '1' then 1 else null end) as [started]
FROM dbo.results AS r
INNER JOIN dbo.member AS f ON r.memberid = f.memberid
Where r.style = 'sled'
AND r.date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY r.Klass Order By r.Klass
2 
SELECT r.Klass,
count(r.Sluttid) + count(Case When r.NF = '1' then 1 else null end) as [started]
FROM dbo.results AS r
INNER JOIN dbo.member AS f ON r.memberid = f.memberid
Where r.style = 'sled'
AND r.date BETWEEN '2025-01-01' AND '2025-12-31'
AND f.birthdate BETWEEN '2012-01-01' AND '2012-12-31' --<<--
GROUP BY r.Klass Order By r.Klass

It can be more efficient to check the date range rather than trying to compare to just year.

3 
Performance Wise 
>= < better than between
  • Thanks for checking out my T‑SQL solution.
  • Really appreciate your time and input.
  • Happy it worked as expected.
  • Always open to tweaks or suggestions.
  • Cheers, Harish
SELECT r.Klass,
       COUNT(*) AS started
FROM results r
JOIN member f ON r.memberid = f.memberid
WHERE r.style = 'sled'
  AND r.date >= '2025-01-01'
  AND r.date < '2026-01-01'
  AND f.born >= '2012-01-01'
  AND f.born < '2012-12-31'
drop table create table with sample data 
DROP TABLE results;
DROP TABLE member

-- Tables
CREATE TABLE member (
    memberid INT PRIMARY KEY,
    name NVARCHAR(50),
    born DATE
);

CREATE TABLE results (
    resultid INT PRIMARY KEY,
    memberid INT,
    Klass NVARCHAR(50),
    style NVARCHAR(20),
    date DATE,
    Sluttid TIME NULL,
    NF CHAR(1) NULL,
    FOREIGN KEY (memberid) REFERENCES member(memberid)
);

-- Sample data
INSERT INTO member VALUES
(1,'Alice','2012-05-12'),
(2,'Bob','2013-07-20'),
(3,'Charlie','2012-11-03');

INSERT INTO results VALUES
(101,1,'Junior','sled','2025-03-15','00:45:00','0'),
(102,2,'Junior','sled','2025-04-10','00:50:00','1'),
(103,3,'Senior','sled','2025-05-05','00:40:00','0'),
(104,1,'Junior','ski','2025-06-01','00:55:00','0');
4

No, they are exactly the same. SQL generates the >= … >= code from the BETWEEN itself.

5

Hi Scott,

You were absolutely right — I was mistaken. The test data clearly shows that BETWEEN and >= … <= perform identically, and your point stands correct. Thanks for clarifying this and helping me see it more clearly.

thanks, Harish

6

Thanks for the help, but it would have been easier to just enter the year of birth between the start/end date, so as not to have to fill in more parameters for the website editor. But let's solve it like this :slight_smile:

7

Actually not an issue. You could use just a year to generate the birthdate range, since that’s better for you.

AND f.birthdate BETWEEN CAST(year_param AS char(4)) + ‘-01-01’ AND CAST(year_param AS char(4)) + ‘-12-31’
8

Thanks :slight_smile:

9

The big thing to avoid if possible is anything like this, because it could have bad performance consequences:

WHERE YEAR(f.birthdate) = 2012