WebDevelopersNotes logo

home-icon Home / MySQL / MySQL development tutorial – MySQL Date column type part 1

MySQL development tutorial – MySQL Date column type part 1

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.

employee_per.dat

These instructions are a repeat of those in session 6 and 8.

Windows system

  1. Move the file to c:\mysql\bin.
  2. Issue the command at DOS prompt.
    dosprompt> mysql employees <employee_per.dat
    
  3. Start mysql client program and check if the table has been created using SHOW TABLES; command.

Linux system

  1. Move to the directory that contains the downloaded files.
  2. At the prompt, type the command below:
    $prompt> mysql employees <employee_per.dat -u your_username -p
    
  3. Check if the table has been created through SHOW TABLES; command in mysql client program.

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.

  • e-id: are employee ids, same as that in table employee_data
  • address: Addresses of employees
  • phone: Phone numbers
  • p_email: Personal email addresses
  • birth_date: Birth dates
  • sex: The sex of the employee, Male or Female
  • m_status: Marital status,Yes or No.
  • s_name: Name of Spouse (NULL if employee is unmarried)
  • children: Number of children (NULL if employee is unmarried)

Characteristics of MySQL Date

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.

Operations on Date

Date column type allow for several operations such as sorting, testing conditions using comparison operators etc.

Using = and != operators on dates

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.

Using >= and <= operators

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)

Specifying date ranges in MySQL

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)

Sponsored Links

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

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 ever text message was sent on December 3, 1992, by Neil Papworth from his computer to the phone of Richard Jarvis. Neil was a test engineer for Sema Group and used the Vodafone network to transmit the text message. [more...]

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