CODEWITHSUNDEEP
sqlsql-server
VAAA
VAAA

Reputation: 15049

SQL Server - Get all persons that turned 25 years old in a selected year

I have a table with 3 fields:

What I need is to create a query where I get all the persons that had their 25th birthday in a selected year.

For example, to know all the persons that had their 25th birthday in the year 2003.

Right now I have this:

DECLARE @dynamicdate DATETIME
SET @dynamicdate = CONVERT(datetime, '20131231')

SELECT *
FROM persons p
WHERE
(year(@dynamicdate-p.birthdate)-1900)=25

But I know its totally wrong and It's should be very simple, any clue?

Upvotes: 0

Views: 1000

Answers (4)

ErikE
ErikE

Reputation: 50282

Do your best to avoid doing calculations or functions on columns used in a WHERE clause. instead, change the expression so the column is by itself one one side of the conditional expression, like below:

DECLARE @BirthYear int = 2003 - 25;

SELECT *
FROM dbo.Persons P
WHERE
   P.BirthDate >= DateAdd(year, @BirthYear - 2000, '20000101')
   AND P.BirthDate < DateAdd(year, @BirthYear - 2000, '20010101')
;

Please pay special attention to the inequality operators I used, >= and <. The best practice for date handling in SQL is to use an inclusive start (greater than or equal to) and an exclusive end (less than, but not equal to). This makes it possible to change the precision of the underlying column, for example to datetime2(7), without having to change your code. It also makes it possible to join ranges contiguously with an equijoin (Prior.ToDate = Next.FromDate) and calculate range intersection correctly (Range1.FromDate < Range2.ToDate AND Range2.FromDate < Range1.ToDate).

If you cannot avoid having an expression on one column or another, put the expression on the column that has no indexes (leaving the column that does have an index by itself). Or, barring that (and this takes a bit of experience to know how to implement), put the expression on the side that in the query plan is earlier in the plan or affects the fewest rows.

Adding indexes, filtered indexes, and calculated persisted columns can all help with performance of often-repeated queries against date columns. But a warning: adding indexes can worsen performance, too.

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146603

Assuming all birthdates are date only, (with no time portion), as they should be). then all you need is to Use this where clause:

where birthdate between 
     dateadd(year, @year-1925, 0) And
     dateAdd(day, -1, dateadd(year, @year-1924, 0))

Upvotes: 0

nassjs
nassjs

Reputation: 1

you can use datepart for passing values(Can pass date too) select * from Person where (datepart(year,'2007-12-31') - datepart(year,birthdate) ) = 25

Upvotes: 0

Todd Bowles
Todd Bowles

Reputation: 1574

You should simply be able to query the year part of the date in question, as you just want to know if the persons 25th birthday fell into that year.

DECLARE @Year AS INT

SET @Year = 2003

SELECT *
FROM Persons P
WHERE ((@Year - DATEPART(yy, birthdate)) = 25)

This SQLFiddle Demonstrates http://sqlfiddle.com/#!6/b040c/2

Keep in mind this is not optimised, but I wouldn't bother doing optimisation until you have actually measured performance and determined that this particular query is a problem. I would focus on the simplest, easiest to read solution first.

Upvotes: 0

Related Questions