What is SQL Server DATEADD() function?

Filed Under: SQL Server
SQL DATEADD() Method

Hey, folks! In this article, we will be focusing on SQL Server DATEADD() function in detail.

What is SQL DATEADD() function?

SQL Server DATEADD() function adds particular value to the date input passed to the function and returns the updated/modified value.

The DIFFADD() function adds a customized value to the portion of timestamp such as year, days, month, week, hours, mins, etc.


Syntax of SQ DATEADD() function

DATEADD(date_section, interval, date);
  • date_section: This includes the portion of the date to which the value would be added.
  • interval: It refers to the value that has to be added to the date portion.
  • date: The date that needs to be passed in the form of “yyyy/mm/dd“.

The DATEADD() function would return the updated timestamp after adding the interval value to it.


Examples of SQL Server DATEADD()

After having understood the syntax of SQL Server DATEADD() function, let us implement the function through various examples.


SQL DATEADD() function with ‘year’ as the date_section

In the below example, we have used ‘year’ as the date portion with the interval of 1 i.e. 1 would be added to the year of the passed timestamp.

SELECT DATEADD(year, 1, '2020/10/15') AS Output;

Output:

2021-10-15T00:00:00Z

SQL Server DATEADD() function with ‘hour’ as the date_section

In this example, we have used ‘hour’ as the date_section i.e. the provided interval would be added to the hour of the timestamp.

SELECT DATEADD(hour, 2, '2020/10/15 17:20:20') AS Output;

Output:

2020-10-15T19:20:20Z

SQL DATEADD() function with ‘month’ as the date_section

Here, we have used month as the date section and an interval of 2 will get added to the month of the passed timestamp.

SELECT DATEADD(month, 2, '2020/10/15 17:20:20') AS Output;

Output:

2020-12-15T17:20:20Z

SQL DATEADD() function with ‘dayofyear’ as the date_section

In this example, we have used dayofyear as the date_section by which the interval adds up to the date value of the timestamp.

SELECT DATEADD(dayofyear, 2, '2020/10/15 17:20:20') AS Output;

Output:

2020-10-17T17:20:20Z

SQL Server DATEADD() function with a negative interval value

Apart from adding interval to the timestamp/date, we can even reduce the date values by subtracting the interval values using a negative interval.

SELECT DATEADD(year, -2, '2020/10/15 17:20:20') AS Output;

As a result, the ‘year’ reduces by an interval of 2 in the output.

Output:

2018-10-15T17:20:20Z

Summary

SQL Server DATEADD() function is extensively used when we want to sum up the current timestamp with a particular value for summarization and predictions through the database.

The DATEADD() function is used to add or subtract intervals from the date passed in the parameter list.


Conclusion

By this, we have come to the end of this topic. Please feel free to comment in case you come across any doubt.

For more of such topics related to SQL, please do visit SQL Server JournalDev.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages