All SQL Date Functions: Syntax and Examples

Introduction

In SQL, dates are a common data type used to store and manipulate temporal information. SQL provides a set of built-in date functions that allow you to perform various operations on date and time values. In this blog post, we will explore the most commonly used SQL date functions, their syntax, and provide examples of how to use them.

1. CURRENT_DATE

The CURRENT_DATE function returns the current date in the format 'YYYY-MM-DD'. It does not require any arguments.

Syntax: CURRENT_DATE

Example: SELECT CURRENT_DATE;

This will return the current date.

2. CURRENT_TIME

The CURRENT_TIME function returns the current time in the format 'HH:MM:SS'. It does not require any arguments.

Syntax: CURRENT_TIME

Example: SELECT CURRENT_TIME;

This will return the current time.

3. CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS'. It does not require any arguments.

Syntax: CURRENT_TIMESTAMP

Example: SELECT CURRENT_TIMESTAMP;

This will return the current date and time.

4. EXTRACT

The EXTRACT function allows you to extract a specific part of a date or time value, such as the year, month, day, hour, minute, or second.

Syntax: EXTRACT(unit FROM date)

Example: SELECT EXTRACT(YEAR FROM '2022-01-01');

This will return the year from the given date.

5. DATEADD

The DATEADD function allows you to add or subtract a specific interval from a date or time value.

Syntax: DATEADD(interval, number, date)

Example: SELECT DATEADD(DAY, 7, '2022-01-01');

This will add 7 days to the given date.

6. DATEDIFF

The DATEDIFF function allows you to calculate the difference between two dates or times.

Syntax: DATEDIFF(interval, start_date, end_date)

Example: SELECT DATEDIFF(DAY, '2022-01-01', '2022-01-08');

This will return the number of days between the two given dates.

7. DATEPART

The DATEPART function allows you to extract a specific part of a date or time value, such as the year, month, day, hour, minute, or second.

Syntax: DATEPART(unit, date)

Example: SELECT DATEPART(YEAR, '2022-01-01');

This will return the year from the given date.

8. DATE_FORMAT

The DATE_FORMAT function allows you to format a date or time value according to a specified format.

Syntax: DATE_FORMAT(date, format)

Example: SELECT DATE_FORMAT('2022-01-01', '%Y-%m-%d');

This will format the given date as 'YYYY-MM-DD'.

9. NOW

The NOW function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS'.

Syntax: NOW()

Example: SELECT NOW();

This will return the current date and time.

10. DAYNAME

The DAYNAME function returns the name of the day of the week for a given date.

Syntax: DAYNAME(date)

Example: SELECT DAYNAME('2022-01-01');

This will return the name of the day for the given date.

Conclusion

SQL provides a variety of date functions that allow you to perform various operations on date and time values. In this blog post, we covered the most commonly used SQL date functions, their syntax, and provided examples of how to use them. By understanding and utilizing these functions, you can effectively manipulate and extract information from date and time data in your SQL queries.