Home / MySQL / MySQL programming – MySQL Date column type part 2
select e_id, birth_date from employee_per ORDER BY birth_date; +------+------------+ | e_id | birth_date | +------+------------+ | 11 | 1957-11-04 | | 16 | 1964-03-06 | | 21 | 1964-06-13 | | 14 | 1965-04-28 | | 15 | 1966-06-23 | | 7 | 1966-08-20 | | 10 | 1967-07-06 | | 20 | 1968-01-25 | | 12 | 1968-02-15 | | 2 | 1968-04-02 | | 9 | 1968-05-19 | | 13 | 1968-09-03 | | 3 | 1968-09-22 | | 6 | 1969-12-31 | | 17 | 1970-04-18 | | 1 | 1972-03-16 | | 4 | 1972-08-09 | | 19 | 1973-01-20 | | 18 | 1973-10-09 | | 5 | 1974-10-13 | | 8 | 1975-01-12 | +------+------------+
Here is how we can select employees born in March.
select e_id, birth_date from employee_per where MONTH(birth_date) = 3; +------+------------+ | e_id | birth_date | +------+------------+ | 1 | 1972-03-16 | | 16 | 1964-03-06 | +------+------------+ 2 rows in set (0.00 sec)
Alternatively, we can use month names instead of numbers.
select e_id, birth_date from employee_per where MONTHNAME(birth_date) = 'January'; +------+------------+ | e_id | birth_date | +------+------------+ | 8 | 1975-01-12 | | 19 | 1973-01-20 | | 20 | 1968-01-25 | +------+------------+ 3 rows in set (0.00 sec)
Be careful when using month names as they are case sensitive. Thus, January will work but JANUARY will not!
Similarly, you can select employees born in a specific year or under specific dates.
select e_id, birth_date from employee_per where year(birth_date) = 1972; +------+------------+ | e_id | birth_date | +------+------------+ | 1 | 1972-03-16 | | 4 | 1972-08-09 | +------+------------+ 2 rows in set (0.00 sec) select e_id, birth_date from employee_per where DAYOFMONTH(birth_date) = 20; +------+------------+ | e_id | birth_date | +------+------------+ | 7 | 1966-08-20 | | 19 | 1973-01-20 | +------+------------+ 2 rows in set (0.00 sec)
Current dates
We had seen in the session on SELECT statement (A little more on the SELECT statement) that current date, month and year can be displayed with CURRENT_DATE argument to DAYOFMONTH(), MONTH() and YEAR() clauses, respectively. The same can be used to select data from tables.
select e_id, birth_date from employee_per where MONTH(birth_date) = MONTH(CURRENT_DATE); +------+------------+ | e_id | birth_date | +------+------------+ | 8 | 1975-01-12 | | 19 | 1973-01-20 | | 20 | 1968-01-25 | +------+------------+ 3 rows in set (0.00 sec)
How do I view a deleted web page?
Is there a way to view a deleted web page - one that is no longer available? Yes there is and the solution is quite simple. [more...]
The Internet started off as ARPANET, the internal network of the Advanced Research Projects Agency. ARPA was established in the U.S. in response to launch of Sputnik 1 by the Soviet Union. [more...]
We use cookies to give you the best possible website experience. By using WebDevelopersNotes.com, you agree to our Privacy Policy