Geek Answers Handbook

If the anniversary comes within n days in MySql

I have a table with anniversary dates. I want a query that returns me the lines of anniversaries that will appear in the next 10 days. For instance:

birthdate
---------
1965-10-10
1982-05-25


SELECT birthdate FROM Anniversaries WHERE mystical_magical_mumbo_jumbo <= 10

+------------+
| birthdate  |
+------------+
| 1982-05-25 |
+------------+
1 row in set (0.01 sec)

I would like to save the request in the form x <= 10, because I will use this number 10 in other parts of the request, and if I set it to a variable, I can change it once everywhere, changing the variable, and there is no need to rewrite the request.

+5
source share
7 answers

As others have stated, you need to ignore the year in comparison. The DAYOFYEAR () function is one way to do this.

. 10 , , - .

, 1 , , . , . - , :)

SELECT birthdate 
FROM Anniversaries 
WHERE dayofyear(birthdate) - dayofyear(curdate()) between 0 and 10 
or dayofyear(birthdate) + 365 - dayofyear(curdate()) between 0 and 10;
+6

SELECT birthdate FROM Anniversaries 
WHERE DATEDIFF(CURTIME(),birthdate) >= 0 
AND DATEDIFF(CURTIME(),birthdate) <= 10

MySQL DATEDIF

DATEDIFF (1, 2)

DATEDIFF() expr1 - expr2, . expr1 expr2 - . .

mysql > SELECT DATEDIFF ('2007-12-31 23:59:59', '2007-12-30');       - > 1

mysql > SELECT DATEDIFF ('2010-11-30 23:59:59', '2010-12-31');       - > -31

+1

, , - , . , . , , , , . :

SELECT Date_Add(birth_date,INTERVAL (Year(now())-Year(birth_date)-1) YEAR) as dt,  
FROM Anniversaries 
WHERE Date_Add(birth_date,INTERVAL (Year(now())-Year(Date)-1) YEAR) 
BETWEEN Date_sub(now(), INTERVAL 7 Day) AND Date_add(now(), INTERVAL 21 Day) 
UNION
SELECT Date_Add(birth_date,INTERVAL (Year(now())-Year(birth_date)) YEAR) as dt,
FROM Anniversaries 
WHERE Date_Add(birth_date,INTERVAL (Year(now())-Year(Date)) YEAR) 
BETWEEN Date_sub(now(), INTERVAL 7 Day) AND Date_add(now(), INTERVAL 21 Day) 
UNION
SELECT Date_Add(birth_date,INTERVAL (Year(now())-Year(birth_date)+1) YEAR) as dt,
FROM Anniversaries 
WHERE Date_Add(birth_date,INTERVAL (Year(now())-Year(Date)+1) YEAR) 
BETWEEN Date_sub(now(), INTERVAL 7 Day) AND Date_add(now(), INTERVAL 21 Day) 
ORDER BY dt

SELECT , SELECT , SELECT , , .

. . , SELECT UNION. , .

+1

, . , (, 2000).

:

SELECT birthdate
FROM Anniversaries
WHERE birthday >= DATE_ADD(MAKEDATE(2000, DAYOFYEAR(CURDATE())), INTERVAL 10 DAYS)

, , 10 . MySQL ( , ).

0

alejandrobog:

SELECT birthdate FROM Anniversaries
WHERE DATEDIFF(MAKEDATE(YEAR(CURRENT_DATE()), DAYOFYEAR(birthdate), CURRENT_DATE()) >= 0
AND   DATEDIFF(MAKEDATE(YEAR(CURRENT_DATE()), DAYOFYEAR(birthdate), CURRENT_DATE()) <= 10
0

MAKEDATE() , , :

SELECT 
    anniversary
    ,  MAKEDATE( YEAR(NOW()), DAYOFYEAR(anniversary) ) AS thisyear 
FROM Anniversaries;

+-------------+-------------+
| anniversary | thisyear    | 
+-------------+-------------+
| 1978-07-29  | 2010-07-29  |
| 1959-04-17  | 2010-04-17  |
+-------------+-------------+

DATEDIFF() ( ):

SELECT 
    anniversary
    , MAKEDATE( YEAR(NOW()), DAYOFYEAR(anniversary) ) AS thisyear
    , DATEDIFF( MAKEDATE(YEAR(NOW()),DAYOFYEAR(anniversary)), NOW()) as days 
FROM Anniversaries;

+-------------+-------------+------+
| anniversary | thisyear    | days | 
+-------------+-------------+------+
| 1978-07-29  | 2010-07-29  |   70 |
| 1959-04-17  | 2010-04-17  |  -33 |
+-------------+-------------+------+

, NOW() 5/20.

, . - , - . "2010-12-31" OR HAVING , , :

SELECT birth_date
, MAKEDATE(YEAR('2010-12-31'),DAYOFYEAR(birth_date)) as anniversary
,  DATEDIFF( MAKEDATE(YEAR('2010-12-31'),DAYOFYEAR(birth_date)), '2010-12-31') as days
, MAKEDATE(YEAR(NOW())+ 1,DAYOFYEAR(birth_date)) as next_anniversary
, DATEDIFF( MAKEDATE(YEAR(NOW())+ 1,DAYOFYEAR(birth_date)), '2010-12-31') as next_days 
FROM Anniversaries 
HAVING ( ( days <= 25 AND days > 0 ) OR next_days <= 25 );

+------------+-------------+------+------------------+-----------+
| birth_date | anniversary | days | next_anniversary | next_days |
+------------+-------------+------+------------------+-----------+
| 2010-01-23 | 2010-01-23  | -342 | 2011-01-23       |        23 |
| 1975-01-11 | 2010-01-11  | -354 | 2011-01-11       |        11 |
+------------+-------------+------+------------------+-----------+
2 rows in set (0.00 sec)
0
     SELECT firstName, lastName, DOB, if( (
     datediff( date( concat( year( current_date ) , '-', month( DOB ) , '-', day( DOB ) ) ) , current_date ) ) >=0, (
    datediff( date( concat( year( current_date ) , '-', month( DOB ) , '-', day( DOB ) ) ) , current_date )), 
    datediff( date( concat( year( current_date ) +1, '-', month( DOB ) , '-', day( DOB ) ) ) , current_date )
)DAYSTOBDAY
    FROM birthinfo
    WHERE (
    if( (
    datediff( date( concat( year( current_date ) , '-', month( DOB ) , '-', day( DOB ) ) ) , current_date ) ) >=0, (
    datediff( date( concat( year( current_date ) , '-', month( DOB ) , '-', day( DOB ) ) ) , current_date )),
    datediff( date( concat( year( current_date ) +1, '-', month( DOB ) , '-', day( DOB ) ) ) , current_date )
    )
    )
    BETWEEN 0
    AND 31

   firstName    lastName    DOB             DAYSTOBDAY
   Michelle     Smith       1979-04-16          17
   Kirsten      Dunst       1982-04-30          31

"" , , , . , . , DOB, .

0

More articles:


All Articles