SQL Date and Times

The manipulation of Dates and Times in SQL can be a little tricky due to multiple formats that a Date and Time data type can have. Be sure that the format of Date and Time you are trying to insert corresponds to the format of Date and Time columns in the database.

SQL Date Data Types

You will often need to store Date and Time values in a database, such as an employee's hiring date, a person's birthdate, a date and time a record is created or modified in a table, and so on.

The Date and Time data type is applied as temporal data, and every database system engine has a storage format and data types to store them.

The following table shows data types supported by MySQL for storing Date or a Date/Time value in the database:

The explanation of the above Date and Time format can be given with the following list:

• DATE values have YYYY-MM-DD format where:
  • YYYY represents the full year part of the date with 4 digits.
  • MM represents the month part of the date with 2 digits.
  • DD represents the day part of the date with 2 digits.
• TIME values have HH:MM:SS format where:
  • HH represents the hours part of the time with 2 digits.
  • MM represents the minutes part of the time with 2 digits.
  • SS represents the seconds part of the time with 2 digits.

The following SQL statement shows how to insert a date in the "Order" table:

INSERT INTO order (id, client_id, order_date)
VALUES (3, 12, '2016-09-24');

Tracking Record Creating or Modification Times

When you work with large data in SQL, you often need to store rows creation/update time in the database.

Using the MySQL database system, the following SQL statement inserts the current timestamp using the NOW() function:

-- MySQL Database Syntax

INSERT INTO order (id, client_id, order_date, created_at)
VALUES (5, 14, '2018-11-24', NOW());

If you want to insert the current date and time automatically, you can use the auto-initialization and auto-update properties of the TIMESTAMP and DATETIME data types.

The following SQL statement specifies the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clause in column definitions, ad follow :

-- MySQL Database Syntax

CREATE TABLE employee (
    id  INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(120) NOT NULL,
    birth_date DATE NOT NULL,
    hire_date DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Extracting Parts of Dates or Times

SQL supports different functions specially designed to extract part of a temporal value. In MySQL you can use the following functions to extract part of Dates and Times values: DAYOFMONTH(), MONTH(), YEAR(), DAYNAME(), MONTHNAME(), HOUR(), MINUTE(), SECOND, ect.

Let us suppose that we have a table called "employee" with the following data:

+--------+------------------+------------+------------+-----------+------------------+
| emp_no | name             | birth_date | hire_date  | salary    | dept_name        |
+--------+------------------+------------+------------+-----------+------------------+
| 1001   | James Smith      | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria Martinez   | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1005   | Donna Williams   | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | James Johnson    | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1007   | Stephanie Miller | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
| 1008   | Nelson Lopez     | 1999-05-26 | 2010-10-06 | 7300      | Marketing        |
+--------+------------------+------------+------------+-----------+------------------+

The following SQL statement will take the year part, the month part, and the day part of the "hire_date" column values.

SELECT name, hire_date, YEAR(hire_date) AS hire year, MONTH(hire_date) AS hire month, hire month, DAYOFMONTH(hire_date) AS hire day
FROM employee; 

After executing the above SQL statement, the output will be as follows:

+------------------+------------+-----------+------------+----------+
| name             | hire_date  | hire year | hire month | hire_day |
+------------------+------------+-----------+------------+----------+
| James Smith      | 2010-05-13 | 2010      | 05         | 13       |
| Maria Martinez   | 2018-10-08 | 2018      | 10         | 08       |
| Andrew Rodriguez | 2019-01-23 | 2019      | 01         | 23       |
| Robert Hernandez | 2019-09-08 | 2019      | 09         | 08       |
| Donna Williams   | 2012-06-06 | 2012      | 06         | 06       |
| James Johnson    | 2014-02-18 | 2014      | 02         | 18       |
| Stephanie Miller | 2009-12-12 | 2009      | 12         | 12       |
| Nelson Lopez     | 2010-10-06 | 2010      | 10         | 06       |
+------------------+------------+-----------+------------+----------+

Formatting Dates or Times

SQL also offers functions to format Dates and Times values. In MySQL, you can use the DATE_FORMAT() and TIME_FORMAT() functions.

The following SQL statement will format the values of the "hire_date" column of the "Employee" table to be more readable, like the value 2018-10-08 to October 08, 2018.

SELECT name, hire_date, DATE_FORMAT(hire_date, '%M %e, %Y');