Geek Answers Handbook

SQL Server 2005, Calculating Upcoming Birthdays by Date of Birth

I have been listening to me for some time. Recently, revising some code that I wrote for a client a few years ago, I was wondering if there is a more elegant solution to the problem.

The client stores all the information about his clients, including the date of birth (date date field)

They run a statement every Monday, which receives any client whose birthday falls over the next week.

those. if the extract was launched on Monday January 1, customers whose birthday fell between (including), Monday, January 8 → Sunday January 14, will be restored.

My solution was to use the Datepart (dy) function and calculate all upcoming birthdays based on the clients ’birthday, converted to the day of the year, adding some logic to be included in the statement, which will be launched at the end of the year. The problem was that using “Day of the Year” gives results for 1 day if the client was born in a leap year and / or the extract starts in the leap year after February 29th, so I had to add more logic again so the procedure returned the expected results.

It seemed completely clogged for what should be a simple task. For simplicity, we can say that the client table contains 4 fields, first name, last name, add and address.

Any suggestions on how to simplify this would really be appreciated.

Wes

+5
5

- ?

select * from Customers c 
where dateadd(year, 1900-year(dob), dob) 
    between dateadd(year, 1900-year(getdate()), getdate())
    and dateadd(year, 1900-year(getdate()), getdate())+7
+4

DATEPART (wk) ?

SET DATEFIRST 1  -- Set first day of week to monday
SELECT * FROM customer
WHERE DATEPART(wk, DATEADD(yy, DATEPART(yy, GETDATE()) - DATEPART(yy, customer.dob), customer.dob)) = DATEPART(wk, GETDATE()) + 1

, , .

+3

, DATEADD .

+2
YEAR(GETDATE() - dbo.Patients.Dob) - 1900

, , 1900 .

+1

, .

SELECT TOP 10 BirthDate, FirstName
FROM Customers
WHERE DATEPART(mm,BirthDate) >= DATEPART(mm,GETDATE())
AND DATEPART(day,BirthDate) >= DATEPART(day,getdate())
OR DATEPART(mm,BirthDate) > DATEPART(mm,getdate())
ORDER BY DatePart(mm,BirthDate),DatePart(day,BirthDate)

,

+1

More articles:


All Articles