Dates in SQL Part-1

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 Datatypes

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

MySQL Date Datatypes

• 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 Datatypes

• 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 Datatypes

• 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 Dates in SQL is fundamental for effective database design and management. SQL provides several date and time data types, including DATE, TIME, DATETIME, and TIMESTAMP, each designed for specific use cases. Furthermore, selecting the appropriate data type improves data integrity, query performance, and storage efficiency. By following best practices and using standard date formats, you can build reliable database applications that handle date and time information accurately.

Knowledge Check

Frequently Asked Questions