SQL Data Types: Date and Time Data Types

Syntax

The syntax for defining a DATE data type is:

DATE

The syntax for defining a TIME data type is:

TIME

The syntax for defining a DATETIME data type is:

DATETIME

Example

DATE data type

CREATE TABLE employees (
  employee_id INT,
  employee_name VARCHAR(50),
  hire_date DATE
);

INSERT INTO employees (employee_id, employee_name, hire_date)
VALUES (1, 'John Doe', '2020-03-15');

TIME data type

CREATE TABLE employees (
  employee_id INT,
  employee_name VARCHAR(50),
  start_time TIME
);

INSERT INTO employees (employee_id, employee_name, start_time)
VALUES (1, 'John Doe', '09:00:00');

DATETIME data type

CREATE TABLE employees (
  employee_id INT,
  employee_name VARCHAR(50),
  start_datetime DATETIME
);

INSERT INTO employees (employee_id, employee_name, start_datetime)
VALUES (1, 'John Doe', '2020-03-15 09:00:00');

Output

When you select data from a table with the DATE, TIME, or DATETIME data types, you'll get a human-readable representation of the date and/or time. For example, if you select a DATE column with the following query:

SELECT hire_date
FROM employees
WHERE employee_id = 1;

You'll get the following output:

+------------+
| hire_date  |
+------------+
| 2020-03-15 |
+------------+

Explanation

The DATE, TIME, and DATETIME data types are used in SQL to represent dates and times.

• The DATE data type represents a date.
• The TIME data type represents a time of day.
• The DATETIME data type represents a date and time together.

All of these data types are stored in a special format that can be easily manipulated and compared by SQL functions.

Use

The DATE, TIME, and DATETIME data types are used to store dates and times in many different types of applications, including:

• Payroll systems
• Booking systems
• Event calendars
• Time-tracking software

If your application needs to work with dates and times, you will likely use one or more of these data types.

Important Points

• The DATE, TIME, and DATETIME data types are used to store dates and times in SQL.
• These data types are stored in a format that can be easily manipulated by SQL functions.
• The DATE data type represents a date.
• The TIME data type represents a time of day.
• The DATETIME data type represents a date and time together.

Summary

In SQL, the DATE, TIME, and DATETIME data types are used to represent dates and times. These data types are stored in a special format that can be easily manipulated by SQL functions. Used in many different types of applications, including payroll systems, booking systems, and time-tracking software, these data types are crucial for accurate tracking and management of time-based data.