Working With Dates in SQL Part-1: A Comprehensive Guide

Working with dates in SQL can often be tricky due to different formats and the need for precise matching between the date value and the column type. This article explores how to manage dates across various SQL databases, covering date data types, manipulation, and conversions.

SQL Date Data Types

Different SQL databases provide specific data types for storing date and time values.

MySQL Date Data Types

• DATE: Stores date values in the format YYYY-MM-DD.
• DATETIME: Stores date and time values in the format YYYY-MM-DD HH:MI:SS.
• TIMESTAMP: Similar to DATETIME, but it updates automatically to the current timestamp on row updates.
• YEAR: Stores year values in YYYY or YY format.

PostgreSQL Date Data Types

• DATE: Stores date values in the format YYYY-MM-DD.
• TIMESTAMP: Stores date and time values in the format YYYY-MM-DD HH:MI:SS.
• TIMESTAMPTZ: Stores timestamp values with time zone information.
• INTERVAL: Represents a duration of time, useful for date arithmetic.

SQL Server Date Data Types

• DATE: Stores date values in the format YYYY-MM-DD.
• DATETIME: Stores date and time values in the format YYYY-MM-DD HH:MI:SS.
• SMALLDATETIME: Stores date and time with a smaller range and lower precision.
• DATETIME2: Stores date and time values with a larger date range and higher precision.
• TIMESTAMP: Used to track row versions, not a date.

Working with Dates

Let’s consider an example Sales table to illustrate how to work with dates.

Sales Table:

Example 1: Selecting Records by Date

To select records with a SaleDate of “2024-09-17”:

SELECT * FROM Sales WHERE SaleDate = '2024-09-17';

Result:

Example 2: Selecting Records with Date and Time

To select records from the Sales table on “2024-09-15”:

SELECT * FROM Sales WHERE DATE(SaleDate) = '2024-09-15';

Result:

Example 3: Using Date Functions

You can also perform operations on dates. Here are some examples:

MySQL: Adding 5 days to the SaleDate.

SELECT SaleId, ProductName, DATE_ADD(SaleDate, INTERVAL 5 DAY) AS NewSaleDate FROM Sales;

PostgreSQL: Subtracting 3 days from the SaleDate.

SELECT SaleId, ProductName, SaleDate - INTERVAL '3 days' AS NewSaleDate FROM Sales;

SQL Server: Adding 10 days to the SaleDate.

SELECT SaleId, ProductName, DATEADD(DAY, 10, SaleDate) AS NewSaleDate FROM Sales;

Date Type Conversions

Date type conversions are essential for ensuring the correct format. Here are examples for converting string values to date formats in different SQL databases:

MySQL: Converting a string to a date.

SELECT STR_TO_DATE('15-09-2024', '%d-%m-%Y') AS ConvertedDate;

PostgreSQL: Converting a string to a date.

SELECT TO_DATE('2024-09-15', 'YYYY-MM-DD') AS ConvertedDate;

SQL Server: Converting a string to a date.

SELECT CONVERT(DATE, '2024-09-15', 120) AS ConvertedDate;

Example 4: Filtering with Date Ranges

To filter sales made in September 2024, you can use the following query:

SELECT * FROM Sales 
WHERE SaleDate BETWEEN '2024-09-01' AND '2024-09-30';

Result:

Conclusion

Understanding how to handle dates in SQL is vital for effective database management. Each SQL database has specific data types and functions for working with dates, and recognizing the correct format is essential for accurate queries. With practice, you can efficiently manage date values and perform operations that meet your data needs.

Knowledge Check