Home / MySQL / MySQL development tutorial – MySQL Date column type part 1
Till now we’ve dealt with text (varchar) and numbers (int) data types. To understand date type, we’ll create one more table.
Download employee_per.dat file below and follow the instructions. The file contain the CREATE table command as well as the INSERT statements.
These instructions are a repeat of those in session 6 and 8.
dosprompt> mysql employees <employee_per.dat
$prompt> mysql employees <employee_per.dat -u your_username -p
Click here for viewing this new table, employee_per.
The details of the table can be displayed with DESCRIBE command.
mysql> DESCRIBE employee_per; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | e_id | int(10) unsigned | | PRI | 0 | | | address | varchar(60) | YES | | NULL | | | phone | int(11) | YES | | NULL | | | p_email | varchar(60) | YES | | NULL | | | birth_date | date | YES | | NULL | | | sex | enum('M','F') | YES | | NULL | | | m_status | enum('Y','N') | YES | | NULL | | | s_name | varchar(40) | YES | | NULL | | | children | int(11) | YES | | NULL | | +------------+------------------+------+-----+---------+-------+ 9 rows in set (0.01 sec)
Notice that column birth_date has date as column type. I’ve also introduced another column type ENUM, which we’ll discuss later.
MySQL dates are ALWAYS represented with the year followed by the month and then the date. Often you’ll find dates written as YYYY-MM-DD, where YYYY is 4 digit year, MM is 2 digit month and DD, 2 digit date. We’ll look at DATE and related column types in the session on column types.
Date column type allow for several operations such as sorting, testing conditions using comparison operators etc.
select p_email, phone from employee_per where birth_date = '1969-12-31'; +---------------------------+---------+ | p_email | phone | +---------------------------+---------+ | killeratlarge@elmmail.com | 6666666 | +---------------------------+---------+ 1 row in set (0.00 sec)
Note: MySQL requires the dates to be enclosed in quotes.
select e_id, birth_date from employee_per where birth_date >= '1970-01-01'; +------+------------+ | e_id | birth_date | +------+------------+ | 1 | 1972-03-16 | | 4 | 1972-08-09 | | 5 | 1974-10-13 | | 8 | 1975-01-12 | | 17 | 1970-04-18 | | 18 | 1973-10-09 | | 19 | 1973-01-20 | +------+------------+ 7 rows in set (0.00 sec)
select e_id, birth_date from employee_per where birth_date BETWEEN '1969-01-01' AND '1974-01-01'; +------+------------+ | e_id | birth_date | +------+------------+ | 1 | 1972-03-16 | | 4 | 1972-08-09 | | 6 | 1969-12-31 | | 17 | 1970-04-18 | | 18 | 1973-10-09 | | 19 | 1973-01-20 | +------+------------+ 6 rows in set (0.00 sec) select e_id, birth_date from employee_per where birth_date >= '1969-01-01' AND birth_date <= '1974-01-01'; +------+------------+ | e_id | birth_date | +------+------------+ | 1 | 1972-03-16 | | 4 | 1972-08-09 | | 6 | 1969-12-31 | | 17 | 1970-04-18 | | 18 | 1973-10-09 | | 19 | 1973-01-20 | +------+------------+ 6 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...]
Email is older than the World Wide Web. It's actually older by two decades. Email was created in 1971 while the web was invented in late 1990. [more...]
We use cookies to give you the best possible website experience. By using WebDevelopersNotes.com, you agree to our Privacy Policy