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

Question

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! :-)

@Steve Hey in SAP all the employees birthdates are saved in a table called OCPR. So imagine one employee has his birthday on 1957.12.25 saved in this table. What is so wrong in that? Sorry if my bad English gave a wrong idea to you :( — Isuru, Jan 15 '16 at 09:27
Try looking at this: http://stackoverflow.com/questions/83531/sql-select-upcoming-birthdays — sr28, Jan 15 '16 at 11:50

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

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.

edited May 23 '17 at 12:31

Community

1
1

answered Jan 15 '16 at 11:28

sr28

4,728
5
36
67

1

This will not give correct result. `BirthDate` can be many years before the current date returned by `GETDATE`. – Vladimir Baranov Jan 15 '16 at 11:43
@VladimirBaranov - just realised that, but just found a question that seems to be very similar. – sr28 Jan 15 '16 at 11:50
@VladimirBaranov - over a normal lifetime this would not have a dramatic effect, (quarter of a day I think). Unless you're planning to record the time of the employees birth as well then for the purpose of being aware of LIVE employees birthdays in a given period this should be fine. However, for the sake of accuracy it may well be worth adding .2425 instead of .25. I shall update my answer. – sr28 Jan 15 '16 at 12:11

Vladimir Baranov · Answer 2 · 2016-01-15T11:39:53.077

I tested it in SQL Server, because it has the DATEADD, GETDATE functions.

Your query returns wrong results when the range of +-30 days goes across the 1st of January, i.e. when the range belongs to two years.

Your calculation

DATEADD(Year, DATEPART(Year, GETDATE()) - DATEPART( Year, T0.[BirthDate]), T0.[BirthDate])

moves the year of the BirthDate into the same year as GETDATE, so if GETDATE returns 2016-01-01, then a BirthDate=1957-12-25 becomes 2016-12-25. But your range is from 2015-12-01 to 2016-01-30 and adjusted BirthDate doesn't fall into it.

There are many ways to take this boundary of the year into account.

One possible variant is to make not one range from 2015-12-01 to 2016-01-30, but three - for the next and previous years as well:

from `2014-12-01` to `2015-01-30`
from `2015-12-01` to `2016-01-30`
from `2016-12-01` to `2017-01-30`

One more note - it is better to compare original BirthDate with the result of some calculations, rather than transform BirthDate and compare result of the function. In the first case optimizer can use index on BirthDate, in the second case it can't.

Here is a full example that I tested in SQL Server 2008.

DECLARE @T TABLE (BirthDate date);

INSERT INTO @T (BirthDate) VALUES
('2016-12-25'),
('2016-01-25'),
('2016-02-25'),
('2016-11-25'),
('2015-12-25'),
('2015-01-25'),
('2015-02-25'),
('2015-11-25'),
('2014-12-25'),
('2014-01-25'),
('2014-02-25'),
('2014-11-25');

--DECLARE @CurrDate date = '2016-01-01';
DECLARE @CurrDate date = '2015-12-31';
DECLARE @VarDays int = 30;

I used a variable @CurrDate instead of GETDATE to check how it works in different cases.

DATEDIFF(year, @CurrDate, BirthDate) is the difference in years between @CurrDate and BirthDate

DATEADD(year, DATEDIFF(year, @CurrDate, BirthDate), @CurrDate) is @CurrDate moved into the same year as BirthDate

The final DATEADD(day, -@VarDays, ...) and DATEADD(day, +@VarDays, ...) make the range of +-@VarDays.

This range is created three times for the "main" and previous and next years.

SELECT
    BirthDate
FROM @T
WHERE
    (
        BirthDate >= DATEADD(day, -@VarDays, DATEADD(year, DATEDIFF(year, @CurrDate, BirthDate), @CurrDate))
        AND
        BirthDate <= DATEADD(day, +@VarDays, DATEADD(year, DATEDIFF(year, @CurrDate, BirthDate), @CurrDate))
    )
    OR
    (
        BirthDate >= DATEADD(day, -@VarDays, DATEADD(year, DATEDIFF(year, @CurrDate, BirthDate)+1, @CurrDate))
        AND
        BirthDate <= DATEADD(day, +@VarDays, DATEADD(year, DATEDIFF(year, @CurrDate, BirthDate)+1, @CurrDate))
    )
    OR
    (
        BirthDate >= DATEADD(day, -@VarDays, DATEADD(year, DATEDIFF(year, @CurrDate, BirthDate)-1, @CurrDate))
        AND
        BirthDate <= DATEADD(day, +@VarDays, DATEADD(year, DATEDIFF(year, @CurrDate, BirthDate)-1, @CurrDate))
    )
;

Result

+------------+
| BirthDate  |
+------------+
| 2016-12-25 |
| 2016-01-25 |
| 2015-12-25 |
| 2015-01-25 |
| 2014-12-25 |
| 2014-01-25 |
+------------+

Thanks for the detailed answer. I'll you know the progress after I test it :) — Isuru, Jan 15 '16 at 11:48

score 0 · Answer 3 · answered Jan 15 '16 at 10:17

0

I've simplified this a little. I have annotated the code where the calculations have been made so looking back in the future you will know what it is doing (often a savior) :).

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 T0.[BirthDate] >= GETDATE()       -- Where the birthday is greater than or equal to today's date
AND   T0.[BirthDate] <= GETDATE() + 30  -- Where the birthday is less than or equal to today's date plus 30 days.

answered Jan 15 '16 at 10:17

Will

228
1
2
15

thanks for the prompt reply. I ran your query in SQL Management Studio. It returns empty results. – Isuru Jan 15 '16 at 10:42
1

This Sql does not check for the year. The year has to be set to the current (and last year if we are in january) year to check it correctly. – etalon11 Jan 15 '16 at 11:51
Also, using the 'AND' means Birthdate would have to equal GETDATE() to ever be returned. – sr28 Jan 15 '16 at 11:56
Apologies, i must have misunderstood what you were trying to do. – Will Jan 15 '16 at 13:45

score 0 · Answer 4 · answered Jan 30 '18 at 11:10

You can try using DAYOFYEAR:

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 IF(DayOfYear(T0.[BirthDate]) - DayOfYear(CURDATE()) < 0, DayOfYear(T0.[BirthDate]) - DayOfYear(CURDATE()) + DayOfYear(DATE_FORMAT(CURDATE(),"%Y-12-31")), DayOfYear(T0.[BirthDate]) - DayOfYear(CURDATE())) <= 30
OR IF(DayOfYear(CURDATE()) - DayOfYear(T0.[BirthDate]) < 0, DayOfYear(CURDATE()) - DayOfYear(T0.[BirthDate]) + DayOfYear(DATE_FORMAT(CURDATE(),"%Y-12-31")), DayOfYear(CURDATE()) - DayOfYear(T0.[BirthDate])) <= 30

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

4 Answers4