SQL Server DATEADD Function

SQL Server DATEADD Function is used to add a day, month, year, hours, minutes, seconds, millisecond, microsecond, or nanosecond to the specified date that is passed as a parameter to the DATEADD function.

Syntax of DATEADD Function in SQL Server:

The syntax of the DATEADD function is:

In the syntax written above:

• Paramter_code: The paramter_code represents that particular parameter of the input date to which incremnet_value is going to be added and that parameter can be anything like day, month, year, hours, minutes, seconds, millisecond, microsecond, or nanosecond of the input date. Each part of the date has a specific code for these parameters and these codes are like:

• Increment_value: The increment_value represents how much value news to be added to that parameter of the input date.
• Input_date: The input_date is the actual input date to which this value will be added and a new date is returned as a result of this function.

How to use the DATEADD function in SQL Server?

Let us understand the usage of the DATEADD function with the help of some examples. Let us run some queries and observe their output to understand the working of the DATEADD function.

As we can see in the above image the input date was the 26th of June month and we get output as 26th of October month. The DATEADD function added four months to the date passed as the third parameter.

In the above query, we added ten days to the current date that is the 26th of June, when we added ten days the month of the resultant date gets changed automatically, the same happens for the year and month also.

Let us create a table named employee having three columns empName for storing the name of the employee and joining_date and work_upto_date to store the employee's joining date and working upto date. The syntax for creating an employee table with three columns named empName, joining_date, work_upto_date is :

As we can see in the image clearly, a table named employee with the above-mentioned schema is created successfully.

So, once our table is ready, now let us add data into this table using the INSERT query. The syntax for INSERT query is:

As we can see in the image, we have successfully inserted eight rows of data in the employee table. But as we can see, we have inserted data only into the empName and joining_date columns of the employee table. Now for adding data in the work_upto_date column of the employee table let us assume a pre-condition that each employee needs to work compulsory for eight months that means the employee is bound to that company for at least eight months from the date of joining of the employee so to calculate the work upto date we will be using the DATEADD function provided by the SQL Server. The syntax of the DATEADD function to calculate the work upto date for each employee in the employee table is:

As we can see in the above image, the data is added successfully in the work_upto_date column of the employee table. The DATEADD function took the date present in the joining_date column and added eight months to that date, and that new resultant date is added as the work upto date in the work_upto_date column of the employee table.

So, this article helps us to have a better perspective about the working and the usage of the COALESCE() Function in SQL Server.