Calculation on Date data type

 
/*

mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT name, birth FROM Bird
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
Empty set (0.00 sec)


*/  
Drop table Bird;

CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ('BlueBird','Joe','Car','f','1999-03-30',NULL);
INSERT INTO  Bird VALUES ('RedBird','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO  Bird VALUES ('RedBird','Yin','Bus','m','1998-01-30',NULL);
  
select * from Bird;

SELECT name, birth FROM Bird
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

Related examples in the same category

1.	Date data type manipulation
2.	Get Day of Month for a date
3.	Get the YEAR part of the date
4.	Birthdays in the upcoming month
5.	DATE_ADD() add a time interval to a given date
6.	Date Calculations: calculate ages
7.	Date Calculations: extract parts of dates
8.	Get sub date
9.	Decomposing Dates or Times Using String Functions substrings.
10.	Pull out the entire date or time part from DATETIME values using string-extraction functions such as LEFT( ) o

Calculation on Date data type : Date Function « Date Time « SQL / MySQL

Related examples in the same category