Home / Computer Science / SQL Server / SQL Date Functions: Syntax and Examples

SQL Date Functions: Syntax and Examples

If you’re working with dates in your SQL queries, you’ll need to be familiar with SQL date functions. These functions allow you to manipulate date values and perform calculations based on them. In this article, we’ll cover some of the most commonly used SQL date functions, along with their syntax and examples.

Introduction to SQL Date Functions

SQL date functions allow you to manipulate date values and perform calculations based on them. These functions can be used in SELECT, INSERT, UPDATE, and DELETE statements, among others.

SQL Date Functions

There are many different SQL date functions, each with its own specific use case. Some of the most commonly used SQL date functions include DATE, DATEADD, DATEDIFF, DAY, MONTH, YEAR, GETDATE, DATEPART, DATENAME, CONVERT, FORMAT, EOMONTH, and ISDATE.

In the following sections, we’ll cover the syntax and examples for each of these functions.

The DATE Function

The DATE function returns a date value based on the year, month, and day values passed as arguments. The syntax for the DATE function is as follows:

DATE(year, month, day)

Here’s an example:

SELECT DATE(2023, 29, 4);

The above SQL Date Function would return the date “2023-29-04”.

The DATEADD Function

The DATEADD function allows you to add a certain number of units (such as days, months, or years) to a date value. The syntax for the DATEADD function is as follows:

DATEADD(interval, number, date)

Here’s an example:

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

This would add 7 days to the date “2022-05-01”, resulting in the date “2022-05-08”.

The DATEDIFF Function

The DATEDIFF function allows you to calculate the difference between two date values, in terms of a specified unit (such as days, months, or years). The syntax for the DATEDIFF function is as follows:

DATEDIFF(interval, date1, date2)

Here’s an example:

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

This would return the value 7, which represents the number of days between the two dates.

The DAY Function

The DAY function returns the day of the month for a given date value. The syntax for the DAY function is as follows:

SELECT DAY(date)

Here’s an example:

SELECT DAY(‘2023-04-29’);

This would return the value 29, which represents the first day of the month.

The MONTH Function

The MONTH function returns the month of the year for a given date value. The syntax for the MONTH function

SELECT MONTH(date)

Here’s an example:

SELECT MONTH('2022-04-29');

This would return the value 4, which represents the month of May.

SELECT MONTH(GETDATE())

This would return the value 4, which represents the month of May.

NOTE: GetDate function returns today’s date.

The YEAR Function

The YEAR function returns the year for a given date value. The syntax for the YEAR function is as follows:

SELECT YEAR(date)

Here’s an example:

SELECT YEAR('2023-04-29');
SELECT YEAR(GETDATE())

This would return the value 2023, which represents the year.

The GETDATE Function

The GETDATE function returns the current date and time. The syntax for the GETDATE function is as follows:

SELECT GETDATE()

Here’s an example:

SELECT GETDATE()

This would return the current date and time value. i.e 2023-04-29 22:17:07.990

The DATEPART Function

The DATEPART function returns a specified part of a date value. The syntax for the DATEPART function is as follows:

DATEPART(interval, date)

Here’s an example:

SELECT DATEPART(month, '2023-04-29');
Output is : 4
SELECT DATEPART(DAY, '2023-04-29');
Output is : 29
SELECT DATEPART(WEEK, '2023-04-29');
Output is : 17 means 17th week oof the year.
SELECT DATEPART(WEEKDAY, '2023-04-29');
output is : 7 means saturday

Observe the above examples how to use DATEPART function.

The DATENAME Function

The DATENAME function returns the name of a specified part of a date value. The syntax for the DATENAME function is as follows:

DATENAME(interval, date)

Here’s an example:

SELECT DATENAME(month, '2023-04-29');
Output is : April
SELECT DATENAME(DAY, '2023-04-29');
Output is : 29
SELECT DATENAME(DAYOFYEAR, '2023-04-29');
Output is : 119 Day of the year from the January 1st.
SELECT DATENAME(WEEK, '2023-04-29');
Output is : 17

The CONVERT Function

The CONVERT function allows you to convert a date value from one data type to another. The syntax for the CONVERT function is as follows:

CONVERT(data_type, expression, style)

Here’s an example:

SELECT CONVERT(varchar, '2022-05-01', 101);

This would convert the date value to a string in the format “mm/dd/yyyy”.

The FORMAT Function

The FORMAT function allows you to format a date value in a specific way. The syntax for the FORMAT function is as follows:

FORMAT(date, format)

Here’s an example:

SELECT FORMAT('2022-05-01', 'MM/dd/yyyy');

This would return the string “05/01/2022”, which represents the date value in the format “MM/dd/yyyy”.

The EOMONTH Function

The EOMONTH function returns the last day of the month for a given date value. The syntax for the EOMONTH function is as follows:

EOMONTH(date)

Here’s an example:

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

This would return the date value “2022-05-31”.

The ISDATE Function

The ISDATE function checks whether a given expression is a valid date value. The syntax for the ISDATE function is as follows:

ISDATE(expression)

Here’s an example:

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

This would return the value 1, indicating that the expression is a valid date value.

Conclusion

In this article, we covered some of the most commonly used SQL date functions, along with their syntax and examples. By using these functions, you can perform calculations and manipulations on date values in your SQL queries. By incorporating them into your queries, you can create more complex and powerful queries that

For the source please visit the official website.
Read SQL string functions here

About Santosh Kumar Gadagamma

I'm Santosh Gadagamma, an Experienced Software Engineer passionate about sharing knowledge in technologies like Java, C/C++, DBMS/RDBMS, Bootstrap, Big Data, Javascript, Android, Spring, Hibernate, Struts, and all levels of software design, development, deployment, and maintenance. I believe computers are essential for the world's functioning, and I'm committed to helping others learn the skills they need to succeed in tech. My website is a valuable learning tool to help you reach greater heights in your education and career, and I believe that education has no end points.

Check Also

Date and Time Functions in SQL Server

Introduction: In the world of databases, managing date and time is a crucial aspect. SQL …