check whether a birth date is upcoming in a data table

Question

This is the question. I am getting the result as number of days from the date of birth till now. but I need whether it is upcoming 5 days from the date now.

select *, datediff(DAY , DateOfBirth,GETDATE()) as diff
    From Table_A
    order by DateOfBirth asc

Mysql does not have getdate is this question tagged correctly? — P.Salmon, Aug 07 '19 at 08:25
Was this supposed to be an answer to someone else's question? — Caius Jard, Aug 07 '19 at 08:26
this is the question. i am getting the result as number of days from the date of birth till now. but i need whether it is upcoming 5 days from the date now — vishal, Aug 07 '19 at 08:31
You could use something like this as a base `SELECT DATEDIFF(DATE_FORMAT(DateOfBirth, CONCAT(YEAR(NOW()),'-%m-%d %T')), NOW()) AS diff` but it doesn't work for cases where the birthday is 1st to 5th of january, for example — Pepper, Aug 07 '19 at 08:36
Can you please reformat question , add some sample input and expected output data — Ven, Aug 07 '19 at 08:44
I think he is trying to get # of days to birthday from today. so will take into consideration the day of the year. — Ed Bangga, Aug 07 '19 at 08:47
Possible duplicate of [SQL Select Upcoming Birthdays](https://stackoverflow.com/questions/83531/sql-select-upcoming-birthdays) — Ed Bangga, Aug 07 '19 at 08:54
@vishal . . . How do you know if someone's date of birth will be five days in the future? The question doesn't make sense. I am guessing you want birthdays. — Gordon Linoff, Aug 07 '19 at 11:22

score 0 · Answer 1 · edited Aug 07 '19 at 09:06

0

SELECT *
FROM Table_A WHERE DateOfBirth BETWEEN GETDATE()
   AND DATEADD(day,5, Convert(Date,GETDATE()))

it will give you all the birthdays in the next 5 days.

edited Aug 07 '19 at 09:06

wonea

4,783
17
86
139

answered Aug 07 '19 at 08:44

Dnyaneshwar Dhatrak

9
1

Dmitrij Kultasev · Answer 2 · 2019-08-12T13:56:27.330

If you are looking for the birthdays, you can do something like:

DECLARE @dates AS TABLE ( DateOfBirth DATE );
DECLARE @number_of_days INT = 5;

INSERT  INTO @dates
        ( DateOfBirth )
VALUES  ( '20090101' ),
        ( '20090809' ),
        ( '20090816' ),
        ( '20180807' );

SELECT  * ,
        DATEDIFF(DAY, SYSDATETIME(), bd.this_year_birthday)
FROM    @dates d
        CROSS APPLY ( SELECT    DATEFROMPARTS(YEAR(SYSDATETIME()),
                                              MONTH(d.DateOfBirth),
                                              DAY(d.DateOfBirth)) AS this_year_birthday
                    ) bd
WHERE   DATEDIFF(DAY, SYSDATETIME(), bd.this_year_birthday) BETWEEN 0
                                                            AND
                                                              @number_of_days;

Samir Selia · Answer 3 · 2019-08-07T09:04:38.127

-1

As per my understanding, you need to get upcoming birthdays in next 5 days. In MySQL you can do with the query below,

SELECT *, DATE_FORMAT(DateOfBirth,'%m-%d') AS md 
FROM Table_A 
WHERE DATE_FORMAT(DateOfBirth,'%m-%d') BETWEEN '08-07' AND '08-12'
ORDER BY md

It will return all birthdays between 07 and 12 Aug. Get the current and +5 days at the scripting side.

If you want pure query based solution without any calculation for dates at scripting side, refer following query,

SELECT *, DATE_FORMAT(DateOfBirth,'%m-%d') AS md 
FROM Table_A 
WHERE DATE_FORMAT(DateOfBirth,'%m-%d') BETWEEN DATE_FORMAT(CURDATE(), '%m-%d')
AND DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 5 DAY), '%m-%d')
ORDER BY md

edited Aug 07 '19 at 09:04

answered Aug 07 '19 at 08:36

Samir Selia

7,007
2
11
30

by reading the comments under the question it seems that it is needed for SQL Server, not mysql – Dmitrij Kultasev Aug 07 '19 at 09:09
It's fine. Someone in the future might bump up to this question for MySQL variant. – Samir Selia Aug 07 '19 at 09:11

check whether a birth date is upcoming in a data table

3 Answers3