Explore SQL Dates with Examples

In this comprehensive guide, we’ll cover various aspects of SQL dates, including date functions, comparison operations and formatting.

Table of Contents

SQL Dates: Data Types

SQL supports various date-related data types, depending on the level of precision and range required:

DATE: Stores a date (year, month, and day).

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_date DATE
);
-- This table stores events with a DATE data type for the event_date column.

INSERT INTO events (event_id, event_name, event_date)
VALUES (1, 'Birthday Party', '2023-09-20');
-- This inserts a date '2023-09-20' into the event_date column.

TIME: Stores a time of day (hour, minute, second).

CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY,
    patient_name VARCHAR(255),
    appointment_time TIME
);
-- This table stores appointments with a TIME data type for the appointment_time column.

INSERT INTO appointments (appointment_id, patient_name, appointment_time)
VALUES (1, 'John Doe', '15:30:00');
-- This inserts a time '15:30:00' into the appointment_time column.

DATETIME or TIMESTAMP: Stores both date and time.

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    transaction_amount DECIMAL(10, 2),
    transaction_datetime DATETIME
);
-- This table stores financial transactions with a DATETIME data type for the transaction_datetime column.

INSERT INTO transactions (transaction_id, transaction_amount, transaction_datetime)
VALUES (1, 100.50, '2023-09-20 09:45:00');
-- This inserts a datetime '2023-09-20 09:45:00' into the transaction_datetime column.

Date Functions

SQL provides a range of date functions to work with date and time values. Here are some common date functions:

CURRENT_DATE(): Returns the current date.
CURRENT_TIME(): Returns the current time.
CURRENT_TIMESTAMP(): Returns the current date and time.
DATE_FORMAT(): Formats a date as a string.
DATE_ADD() and DATE_SUB(): Add or subtract intervals from a date.
EXTRACT(): Extracts date components like year, month, or day from a date.

Date Comparison Operators

Date values can be compared using various operators:

Equal To (=): Compares if two date values are equal.

SELECT *
FROM orders
WHERE order_date = '2023-09-15';
-- This query retrieves all orders placed on September 15, 2023.

Not Equal To (!= or <>): Checks if two dates are not equal.

SELECT *
FROM events
WHERE event_date != '2023-09-20';
-- This query retrieves all events that do not occur on September 20, 2023.

Greater Than (>): Compares if the left date is later than the right date.

SELECT *
FROM appointments
WHERE appointment_date > '2023-09-30';
-- This query retrieves all appointments scheduled after September 30, 2023.

Less Than (<): Compares if the left date is earlier than the right date.

SELECT *
FROM tasks
WHERE due_date < '2023-10-15';
-- This query retrieves all tasks with due dates before October 15, 2023.

Greater Than or Equal To (>=): Checks if the left date is later than or equal to the right date.

SELECT *
FROM birthdays
WHERE birth_date >= '2000-01-01';
-- This query retrieves all people born on or after January 1, 2000.

Less Than or Equal To (<=): Checks if the left date is earlier than or equal to the right date.

SELECT *
FROM promotions
WHERE start_date <= '2023-09-15';
-- This query retrieves all promotions starting on or before September 15, 2023.

Date Ranges Using BETWEEN

The BETWEEN operator is used to specify a range of values. You can use it to filter date values within a specified range.

Example:

SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

Date Formatting

Date formatting is essential for displaying date values in a human-readable format. You can use the DATE_FORMAT() function or database-specific formatting functions to achieve this.

Examples:

-- Basic Date Formatting:
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted_date;

formatted_date
--------------
2023-09-15

-- Formatting with Day Name:
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y') AS formatted_date;

formatted_date
---------------
Thursday, September 15, 2023

--12-Hour Time Format with AM/PM: 
SELECT DATE_FORMAT(NOW(), '%h:%i %p') AS formatted_time;

formatted_time
--------------
03:30 PM

--Formatting Dates from a Table:
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

Conclusion: SQL Dates

In conclusion, this comprehensive guide has provided a detailed exploration of SQL dates, covering essential aspects such as date data types, date functions, date comparison operators, and date formatting. Here are the key takeaways:

Date Data Types: SQL offers various date-related data types, including DATE, TIME, and DATETIME (or TIMESTAMP), to suit different precision and range requirements. Examples demonstrated how to create tables with these data types and insert date, time, and datetime values into them.
Date Functions: SQL provides a range of date functions like CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), DATE_FORMAT(), DATE_ADD(), DATE_SUB(), and EXTRACT(). These functions enable manipulation and extraction of date and time components for various use cases.
Date Comparison Operators: Date values can be compared using operators such as equal to (=), not equal to (!= or <>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=). Examples illustrated how to use these operators to filter data based on date conditions.
Date Ranges Using BETWEEN: The BETWEEN operator is useful for specifying date ranges. It allows you to filter date values falling within a specified range, as demonstrated in an example filtering orders within a specific month.
Date Formatting: Proper date formatting is crucial for presenting date values in a human-readable format. The DATE_FORMAT() function and database-specific formatting functions were discussed, with an example showcasing date formatting in MySQL.

Understanding SQL dates and how to work with them is fundamental for database operations involving time-sensitive data.

Related Articles: