WebDevelopersNotes logo

home-icon Home / MySQL / MySQL programming – MySQL Date column type part 2

MySQL programming – MySQL Date column type part 2

MySQL programming - MySQL Date column type part 2

Using Date to sort data

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 |
+------+------------+

Selecting data using Dates

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)

Sponsored Links

Your comments
Star icon IMPORTANT Have a question / problem? Click here to ask an expert.

Etch-A-Sketch iPad case

Sponsored Links

Tips

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...]

Fact

The first user of the internet was Charley Kline, a student of the University of California, Los Angeles. He sent the first message which consisted of just five characters - "login". It was transmitted over the ARPANET, the network of the Advanced Research Projects Agency and the precursor of the Internet. The message was sent on 29th October 1969 from UCLA's SDS Sigma 7 Host computer to the Stanford Research Institute's SDS 940 Host computer.Technically, at that time, ARPANET wasn't the Internet, as we know it, because it wasn't running the TCP/IP protocol! [more...]

We use cookies to give you the best possible website experience. By using WebDevelopersNotes.com, you agree to our Privacy Policy