All SQL Date Functions: Syntax and Examples
Master SQL date functions with our comprehensive guide. Explore syntax and practical examples for seamless date manipulation. Enhance your database skills now!
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.