SQL Dates

Introduction to SQL Dates

SQL provides a variety of date and time data types to store date and time values. Understanding how to work with these data types is essential for managing time-sensitive data in your applications.

Date Data Types

The primary date and time data types in SQL include:

• DATE: Stores dates in the format 'YYYY-MM-DD' (e.g., '2024-08-13').
• TIME: Stores time in the format 'HH:MM:SS' (e.g., '14:30:00').
• DATETIME: Combines date and time (e.g., '2024-08-13 14:30:00').
• TIMESTAMP: Similar to DATETIME, but automatically updates when a record is modified.
• YEAR: Stores a year in 2-digit or 4-digit format (e.g., '24' or '2024').

Creating a Table with Date Fields

You can create a table that includes date fields using the following SQL command:

CREATE TABLE Events (
    EventID INT AUTO_INCREMENT PRIMARY KEY,
    EventName VARCHAR(100),
    EventDate DATE,
    EventTime TIME,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Inserting Dates into a Table

When inserting records, ensure that the date format is correct:

INSERT INTO Events (EventName, EventDate, EventTime)
VALUES ('Birthday Party', '2024-09-15', '18:00:00');

Retrieving and Formatting Dates

You can retrieve and format dates using the DATE_FORMAT() function:

SELECT EventName, 
       DATE_FORMAT(EventDate, '%M %d, %Y') AS FormattedDate
FROM Events;

This will return the event date in a more readable format (e.g., 'September 15, 2024').

Date Functions

SQL provides various functions to work with dates:

• CURRENT_DATE: Returns the current date.
• CURRENT_TIME: Returns the current time.
• CURRENT_TIMESTAMP: Returns the current date and time.
• DATE_ADD(): Adds a time interval to a date.
• DATE_SUB(): Subtracts a time interval from a date.

Example: Adding Days to a Date

SELECT EventName, 
       DATE_ADD(EventDate, INTERVAL 7 DAY) AS OneWeekLater
FROM Events;

This query adds 7 days to each event date.

Example: Subtracting Days from a Date

SELECT EventName, 
       DATE_SUB(EventDate, INTERVAL 1 MONTH) AS OneMonthEarlier
FROM Events;

This query subtracts one month from each event date.

Date Comparison

You can also compare dates in SQL queries:

SELECT EventName 
FROM Events 
WHERE EventDate > '2024-08-01';

This will return events that occur after August 1, 2024.

Conclusion

Working with dates in SQL is crucial for managing time-sensitive data. By understanding date data types, insertion, retrieval, and manipulation functions, you can efficiently handle date-related operations in your database.