CODEWITHSUNDEEP
sqlsql-servert-sql
André Alçada Padez
André Alçada Padez

Reputation: 11598

SQL Date Diff disregarding Year

i want to make a select, where the users birthday(date field) is less than 30 days.
what is the best way to to do it? i tried datediff, but i don't know how to put the year aside.

Thanks

Upvotes: 1

Views: 428

Answers (3)

Rahul Biswas
Rahul Biswas

Reputation: 43

SELECT *
FROM dbo.CheckBirthDay
WHERE (CASE WHEN YEAR(BirthDay) <= YEAR(CURRENT_TIMESTAMP) THEN DATEDIFF(DD,BirthDay,CURRENT_TIMESTAMP) END < 30 )

Upvotes: 0

Oleks
Oleks

Reputation: 32333

You could just use DATEPART function with dayofyear datepart value.

EDIT: honestly, there is a boundary issue in my previous answer (many thanks to Damien): e.g. 2010-12-25 and 2011-01-07 => the difference should be less then 30 days, but DATEPART(dayofyear, @date) - DATEPART(dayofyear, [Birthday]) < 30 condition would skip this record. So I added an additional contition to my answer:

DATEPART(dy, @d) - DATEPART(dy, [Birthday]) < 30 OR
(
   DATEPART(mm, @d) = 12 AND
   DATEPART(dy, DATEADD(m, 1, @d)) - DATEPART(dy, DATEADD(m, 1, [Birthday])) < 30
)

it adds one month to the each date in the case when the month part of the first date is December and compares the difference.

Upvotes: 5

bart
bart

Reputation: 7767

A common way is to compose a formatted date, as text, and replace the year with the current year; and parse back into a date. Apply datediff on that.

If you find out datediff returns something negative thus the birthday of this year is in the past, add 1 year, and try again. This is for the time period around New Year.

Upvotes: 2

Related Questions