SQL Dates

SQL provides several functions and types to work with dates and times. Here's an overview along with examples and their outputs.

Date and Time Data Types

• DATE: Stores date values (year, month, day).
• TIME: Stores time values (hour, minute, second).
• DATETIME: Stores date and time values.
• TIMESTAMP: Stores a timestamp value that includes date and time.
• YEAR: Stores a year value.

Getting the Current Date and Time

• CURDATE(): Returns the current date.
• CURTIME(): Returns the current time.
• NOW(): Returns the current date and time.
• CURRENT_TIMESTAMP(): Synonym for NOW().

Example

SELECT CURDATE(); -- Output: '2024-07-25'
SELECT CURTIME(); -- Output: '14:35:06'
SELECT NOW();     -- Output: '2024-07-25 14:35:06'

Date and Time Functions

• DATE(): Extracts the date part of a datetime expression.
• TIME(): Extracts the time part of a datetime expression.
• YEAR(), MONTH(), DAY(): Extracts the year, month, and day parts respectively.

Example

SELECT DATE('2024-07-25 14:35:06'); -- Output: '2024-07-25'
SELECT TIME('2024-07-25 14:35:06'); -- Output: '14:35:06'
SELECT YEAR('2024-07-25');          -- Output: 2024
SELECT MONTH('2024-07-25');         -- Output: 7
SELECT DAY('2024-07-25');           -- Output: 25

Date Arithmetic

• DATE_ADD(), DATE_SUB(): Adds or subtracts a date or time interval.
• DATEDIFF(): Returns the number of days between two dates.

Example

SELECT DATE_ADD('2024-07-25', INTERVAL 10 DAY); -- Output: '2024-08-04'
SELECT DATE_SUB('2024-07-25', INTERVAL 10 DAY); -- Output: '2024-07-15'
SELECT DATEDIFF('2024-08-04', '2024-07-25');    -- Output: 10

Date Formatting

• DATE_FORMAT(): Formats a date according to a specified format.

Example

SELECT DATE_FORMAT('2024-07-25', '%W, %M %d, %Y'); -- Output: 'Thursday, July 25, 2024'

Practical Use Case

Suppose we have a table called orders with a column order_date of type DATETIME. We want to get all orders from the last 30 days.

Example Query

SELECT * FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

Combining Date and Time Functions

You can combine multiple functions to achieve complex queries. For example, finding the difference in hours between two DATETIME values.

Example

SELECT TIMESTAMPDIFF(HOUR, '2024-07-25 10:00:00', '2024-07-25 14:00:00'); -- Output: 4

Summary

SQL provides robust support for date and time manipulation. Understanding these functions and types allows you to perform various operations, such as extracting parts of dates, formatting dates, adding/subtracting intervals, and more.