SQL Date and Time Data Types | Complete Beginner's Guide

1. DATE Data Type

The DATE type is used to store calendar dates (year, month, and day) without any time component.

column_name DATE;

CREATE TABLE employees (
id INT,
hire_date DATE
   );

2. TIME Data Type

The TIME type stores only the time of day — without any reference to dates.

column_name TIME;

CREATE TABLE work_shifts (
id INT,
shift_start TIME,
shift_end TIME
      );

3. DATETIME Data Type

The DATETIME type combines both date and time into a single field.

column_name DATETIME;

CREATE TABLE bookings (
id INT,
booking_time DATETIME
     );

4. TIMESTAMP Data Type

The TIMESTAMP type also stores date and time, but it is typically used for recording the exact moment an event happens, especially with automatic time tracking.

column_name TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

CREATE TABLE user_logins (
id INT,
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     );

5. YEAR Data Type

The YEAR type stores only a year value.

column_name YEAR;

CREATE TABLE models (
id INT,
model_year YEAR
      );

Quick Comparison Table

Important Tips for Working with Date/Time Types

• Always use DATETIME when you need both date and time without timezone handling
• Use TIMESTAMP if you want automatic updates based on the current time (especially in MySQL)
• Remember that TIMESTAMP has a limited range (up to 2038)
• For heavy date/time queries, indexing the date fields can greatly improve performance
• Consider timezone implications when storing timestamps for global applications
• Use appropriate date/time functions for calculations rather than manual operations