SQL Date Functions are essential for managing and manipulating date and time values in SQL databases. They provide tools to perform operations such as calculating date differences, retrieving current dates and times and formatting dates. From tracking sales trends to calculating project deadlines, working with temporal data is central to decision-making and reporting.

In this article, we will explain these functions in detail, demonstrating their applications through practical examples using a sales table.

SQL Date Functions

SQL Date Functions are built-in utilities provided by SQL databases to perform operations on date and time values. These functions simplify handling temporal data, making them indispensable for tasks like calculating intervals, extracting year or month values, and formatting dates for display.

Let's use the salestable as the base for demonstrating each of the SQL Date Functions mentioned. Here’s the structure of the sales table:

1. NOW()

The NOW() function returns the current date and time based on the server’s time zone. It's commonly used when we need to capture the exact moment an event occurs, such as a transaction timestamp. This function is useful for logging timestamps or comparing current time with database records.

Query:

SELECT NOW() AS current_datetime;

Output

2. CURDATE()

The CURDATE() function returns the current date in the YYYY-MM-DDformat. It is useful when we need to retrieve only the current date without the time. This function is often used in reporting or filtering records by date.

Query:

SELECT CURDATE() AS current_date;

Output

3. CURTIME()

The CURTIME() function returns the current time in the HH:MM:SS format. It is useful for time-specific operations, such as scheduling tasks. By isolating the time, this function helps in scenarios requiring precise time comparisons.

Query:

SELECT CURTIME() AS current_time;

Output

4. DATE()

TheDATE() function extracts the date portion from a date or datetime expression. This function is useful when we want to ignore the time component and focus only on the date. This function is commonly employed in date-only comparisons or aggregations.

Query:

SELECT sale_id, product_name, 
DATE(sale_date) AS sale_date_only 
FROM sales;

Output

5. EXTRACT()

The EXTRACT() function allows us to retrieve a specific part (like year, month, or day) from a date. It's particularly useful when we need to group or filter data based on specific time components. This function is especially useful in reports that require year-over-year analysis.

Query:

SELECT sale_id, product_name, 
EXTRACT(YEAR FROM sale_date) 
AS sale_year FROM sales;

Output

6. DATE_ADD()

The DATE_ADD() function adds a specified time interval (like days, months, or years) to a date. It's often used in scenarios where we need to calculate a future date. This function simplifies future date calculations for planning purposes.

Query:

SELECT sale_id, product_name, DATE_ADD(sale_date, INTERVAL 7 DAY) AS sale_date_plus_7_days FROM sales;

Output

7. DATE_SUB()

TheDATE_SUB()function subtracts a specified time interval from a date. It's handy when we need to determine a past date by subtracting days, months, or years. This is often used for retrospective data analysis.

Query:

SELECT sale_id, product_name, 
DATE_SUB(sale_date, INTERVAL 3 DAY) 
AS sale_date_minus_3_days 
FROM sales;

Output

8. DATEDIFF()

The DATEDIFF() function returns the difference in days between two dates. It's commonly used to calculate the duration between two events or dates. This function is ideal for deadline tracking or overdue calculations.

Query:

SELECT sale_id, product_name, 
DATE_FORMAT(sale_date, '%W, %M %d, %Y') AS formatted_sale_date 
FROM sales;

Output

9. DATE_FORMAT()

The DATE_FORMAT() function formats a date according to a specified format, allowing for customized date output (e.g. displaying the full day name, month name etc). This function is excellent for improving report readability.

Query:

SELECT sale_id, product_name, 
DATE_FORMAT(sale_date, '%W, %M %d, %Y') 
AS formatted_sale_date FROM sales;

Output

10. ADDDATE()

The ADDDATE()function adds a specified time interval to a date. It is useful for calculating future or past dates based on a given date.

Query:

SELECT sale_id, product_name, 
ADDDATE(sale_date, INTERVAL 10 DAY) 
AS sale_date_plus_10_days 
FROM sales;

Output

11. ADDTIME()

The ADDTIME()function adds a specified time interval to a time or datetime value. It is useful for adjusting times by adding hours, minutes or seconds.

Query:

SELECT sale_id, product_name, ADDTIME('10:30:00', '02:30:00') AS sale_time_plus_2hrs_30min FROM sales;

Output

Conclusion

SQL Date Functions are powerful tools for managing and analysing temporal data. They streamline operations ranging from simple date extractions to complex interval calculations. Mastering these functions empowers users to derive meaningful insights and create accurate, dynamic reports. By integrating these functions into our queries, we can enhance both efficiency and clarity in handling date and time data.