SQL | Date Functions

SQL Date Functions are built-in tools used to manage and manipulate date and time values in a database. They are essential for performing operations like retrieving current dates, calculating differences and formatting results

• Calculate date and time differences (e.g., days between orders).
• Retrieve the current system date and time.
• Extract specific parts of a date (year, month, day).
• Format dates for user-friendly display.
• Track trends, deadlines, or schedules in business processes.

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 is 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-DD format. 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()

The DATE() 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 is 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()

The DATE_SUB()function subtracts a specified time interval from a date. It is 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 is commonly used to calculate the duration between two events or dates. This function is ideal for deadline tracking or overdue calculations. Here we will find out how many days are left from each sale till August 15, 2024.

Query:

SELECT sale_id, product_name, sale_date,
DATEDIFF('2024-08-15', sale_date) AS days_until_aug15
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