Date Functions in SQL

A date function in SQL is a pre-built function that operates on date and time information. With the help of these functions, you may modify date and time information, carry out computations, and extract particular elements from dates, like the day, month, or year. In SQL, common date functions include:

• CURRENT_DATE(): returns the current date.
• CURRENT_TIME(): returns the current time.
• CURRENT_TIMESTAMP(): returns the current date and time.
• DATEADD(): adds a specified interval to a date.
• DATEDIFF(): returns the difference between two dates.
• DATEPART(): returns a specific part of a date, such as the year, month, or day.
• DAY(): returns the day of the month from a given date.
• MONTH(): returns the month from a given date.
• YEAR(): returns the year from a given date.

In data analysis and reporting, where dates are frequently used to track patterns over time, date functions are very helpful. Use a date function, for instance, to determine how many days there are between two dates or to separate the month and year from a date for grouping and analysis needs. Additionally, to do more intricate calculations on date and time data, date functions can be used in conjunction with other SQL functions like aggregate functions and window functions. You can execute operations on dates and times using the many date functions that SQL offers. Here are a few samples, data types, and typical date functions:

1. Data Types:

• DATE: represents a date in the format of YYYY-MM-DD.
• TIME: represents a time in the format of HH:MM:SS.
• DATETIME: represents both date and time in the format of YYYY-MM-DD HH:MM:SS.
• TIMESTAMP: represents a date and time, similar to DATETIME.

2. Functions:

• CURRENT_DATE(): returns the current date.
• CURRENT_TIME(): returns the current time.
• CURRENT_TIMESTAMP(): returns the current date and time.
• DATEADD(): adds a specified interval to a date.
• DATEDIFF(): returns the difference between two dates.
• DATEPART(): returns a specific part of a date, such as the year, month, or day.
• DAY(): returns the day of the month from a given date.
• MONTH(): returns the month from a given date.
• YEAR(): returns the year from a given date.
• 
  
• Example 1: Using CURRENT_DATE()

SELECT CURRENT_DATE();

This query will return the current date in the format of YYYY-MM-DD.

• Example 2: Using DATEADD()

SELECT DATEADD(day, 7, '2022-05-01');

This query will add 7 days to the date '2022-05-01' and return the result in the format of YYYY-MM-DD.

• Example 3: Using DATEDIFF()

SELECT DATEDIFF(day, '2022-05-01', '2022-05-08');

This query will return the difference between the two dates in days.

• Example 4: Using DATEPART()

SELECT DATEPART(month, '2022-05-01');

This query will return the month of the date as an integer, in this case, the value 5 for May.

• Example 5: Using YEAR()

SELECT YEAR('2022-05-01');

This query will return the year of the date as an integer, in this case, the value 2022.

Example 4. CURRENT_DATE(): Returns the current date.

SELECT CURRENT_DATE();

Output: 2023-04-24

Example 5. DATEADD(): Adds a specified interval to a date.

SELECT DATEADD(day, 1, '2023-04-24');

Output: 2023-04-25

Example 6. DATEDIFF(): Calculates the difference between two dates.

SELECT DATEDIFF(day, '2023-04-24', '2023-04-28');

Output: 4

Example 7. YEAR(): Extracts the year from a date.

SELECT YEAR('2023-04-24');

Output: 2023

Example 8. DATE: Stores a date value.

CREATE TABLE example (id INT, date_col DATE);

Example 9. TIMESTAMP: Stores a date and time value.

CREATE TABLE example (id INT, timestamp_col TIMESTAMP);

Example 10. SELECT * FROM example WHERE date_col = '2023-04-24';

This query will return all rows from the table 'example' where the 'date_col' value is '2023-04-24'.

Example 11. SELECT DATE_FORMAT('2023-04-24', '%Y-%m-%d');

This query will format the date '2023-04-24' as '2023-04-24'.

Numerous applications, such as financial reporting, scheduling, and tracking, might benefit from date functions.