sqlsapb1

Get Birthdates of employees for a period of +-30 days

I'm a newbie in SQL. For my SAP B1 add-on I need a SQL query to display Birthdates of employees for a period of +-30days(this will be a user given int at the end).

I wrote a query according to my understanding and it only limits the period only for the current month. Ex:If the current date is 2016.01.15 the correct query should show birthdates between the period of 16th December to 14th February. But I only see the birthdates for January.You can see the query below.

SELECT T0.[BirthDate], T0.[CardCode], T1.[CardName], T0.[Name], T0.[Tel1], 
T0.[E_MailL] FROM OCPR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 
WHERE DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, T0.[BirthDate]), 
T0.[BirthDate]) BETWEEN CONVERT( DATE, GETDATE()-30)AND CONVERT( DATE, GETDATE() +30);

What are the changes I should do to get the correct result? Any help would be highly appreciated! :-)

Solution

  • How about something like this:

    SELECT T0.[BirthDate], T0.[CardCode], T1.[CardName], T0.[Name], T0.[Tel1], T0.[E_MailL] 
FROM OCPR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 
WHERE TO.[BirthDate] BETWEEN DATEADD(DAY, -30, GETDATE()) AND DATEADD(DAY, +30, GETDATE())

    You can adapt the answer I've referenced in the comments as follows:

    SELECT T0.[BirthDate], T0.[CardCode], T1.[CardName], T0.[Name], T0.[Tel1], T0.[E_MailL]
FROM OCPR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE 1 = (FLOOR(DATEDIFF(dd,TO.Birthdate,GETDATE()+30) / 365.25))
          -
          (FLOOR(DATEDIFF(dd,TO.Birthdate,GETDATE()-30) / 365.25))

    As per Vladimir's comment you can amend the '365.25' to '365.2425' for better accuracy if needed.