Use dateadd and datediff to calculate datetime values of SQL Server

 

In the SQL Server database, values of datetime and smalldatetime are stored as integers. However, unlike integers, they cannot be directly computed in mathematics. However, sometimes you still need to add or subtract a time interval in the date/time value. For example, you may want to add a number of months, days, or even hours to a value. You may even want to compare two date/time values to determine the time interval between them, such as the number of days or years of difference. To simplify these types of computing, transact-SQL supports two important date/time Methods: dateadd and datediff.

 

In the fourth part of a series of articles on datetime values, I have explained how to use these two methods and how they work. To demonstrate these methods, I used the following Transact-SQL code to create a sales. Orders table in the adventureworks sample database:

 

Use adventureworks Go If exists (select table_name From information_schema.tables Where table_schema = 'sales' And table_name = 'Orders ') Drop table sales. Orders Go Create Table sales. Orders ( Orderid int not null, Orderdate datetime not null, Delivdate datetime not null ) Go Insert into sales. Orders Values (1001, getdate (), '2017-09-08 18:27:10. 123 ')

 

The table definition includes the orderdate and delivdate fields, both of which are datetime data types. After I create a table, I insert a row in the table to test the dateadd and datediff methods.

 

Use the dateadd Method

 

In some cases, you may want to add a time interval to the datetime or smadddatetime value -- or subtract a time interval. For example, you may need to add or subtract a month from a specified date. You can use the dateadd method to execute this computation. This method uses the following syntax:

 

Dateadd (<date/time_part>, <number>, <date>)

 

<Date/time_part> A placeholder refers to the increment/remainder difference (such as day or month) that is added or reduced in the date/time value ). The following table lists the available date/time sections and abbreviations of these sections:

 

Date/time part	Abbreviations
Year	YY, yyyy
Quarter	QQ, Q
Month	Mm, m
Day of year	Dy, y
Day	DD, d
Week	Wk, WW
Weekday	DW
Hour	HH
Minute	Mi, n
Second	SS, S
Millisecond	MS

 

For example, if you want to add an hour to a date/time value, you can use the HH abbreviation. In some cases, the date/time Section supports two abbreviations, for example, weeks can be supported by wk or ww.

 

<Number> A placeholder refers to the added value (an integer ). For example, if you add 10 days to a date, it is 10. However, note that if the time interval is subtracted, it must be a negative integer. For example, if you subtract 10 from the number of days, the value must be-10.

 

<Date> A placeholder refers to the date/time value of the specified interval to increase or decrease. It may be a string value in date/time format, a date/time value returned by the method, or a common datetime or smalldatetime field.

 

Let's give an example to illustrate how it works. In the following SELECT statement, I add the orderdate value from three months to the sales. Orders table:

 

Select orderdate, dateadd (mm, 3, orderdate) as newdate From sales. Orders Where orderid = 1001.

 

Note that the select list uses the dateadd method. This method has three parameters: Mm indicates month, 3 indicates month, and orderdate indicates a datetime value. Therefore, when the return value is queried, each orderdate value is increased by three months, as shown in the following results:

 

Orderdate	Newdate
2008-08-27 13:36:16. 280	13:36:16. 280

 

As shown above, the date August 27 has been changed to November 27. In addition, such operations are not limited to dates. Next I will add three hours to the orderdate value:

 

Select orderdate, dateadd (HH, 3, orderdate) as newtime From sales. Orders Where orderid = 1001.

 

The first parameter of dateadd is HH instead of MM. Therefore, only the hour is changed, as shown in the following result:

 

Orderdate	Newtime
2008-08-27 13:36:16. 280	2008-08-27 16:36:16. 280

 

The date/time value can also be subtracted from a certain date or time interval. In the following example, I subtract three days from the orderdate value:

 

Select orderdate, dateadd (DD,-3, orderdate) as pastdate From sales. Orders Where orderid = 1001.

 

Note that the first parameter of dateadd is dd. At the same time, note that the second parameter is a negative number, which means that three days will be subtracted, as shown below:

 

Orderdate	Pastdate
2008-08-27 13:36:16. 280	2008-08-24 13:36:16. 280

 

In this way, the new date is August 24 instead of August 27.

 

In this way, the example above demonstrates how to modify the date/time value after it is queried from the database. The dateadd method can also be used to insert date/time data. Because the dateadd method returns a datetime value. (If the method corresponding to the provided date is smalldatetime, it returns a smalldatetime value .) In the following example, I add a row of data to the sales. Orders table, and then use the SELECT statement to retrieve this row:

 

Insert into sales. Orders Values (1002, getdate (), dateadd (DD, 10, getdate ())) Go Select * from sales. Orders Where orderid = 1002.

 

Note that the values clause contains the values of each field in the table. For the orderdate value, I use the getdate () method to obtain the current date and time. For the delivdate field, I use the dateadd method and the corresponding three parameters. The first parameter dd indicates the number of days to be added to the date. The second parameter 10 means that 10 days will be added to the date. Finally, the third parameter is the getdate method. Therefore, it will be added to the current date and time in 10 days and inserted into the delivdate field. This is the result of the SELECT statement:

 

Orderid	Orderdate	Delivdate
1002	2008-08-27 13:40:22. 357	2008-09-06 13:40:22. 357

 

As expected, the delivdate value is 10 days later than orderdate.

 

Now let's check an update statement using the dateadd method. In the following statement, I subtract three days from the delivdate value, and then the result is displayed:

 

Update sales. Orders Set delivdate = dateadd (DD,-3, delivdate) Where orderid = 1002. Go Select * from sales. Orders Where orderid = 1002.

 

This time, I used dateadd in the set clause -- I set the delivdate value to the result returned by the dateadd method. This method specifies the number of days (dd) as the first parameter,-3 as the second parameter, and the delivdate field as the third parameter. This means that the method returns a date three days earlier than the original date, and sets the delivdate as the new date. The result is as follows:

 

Orderid	Orderdate	Delivdate
1002	2008-08-27 13:40:22. 357	2008-09-03 13:40:22. 357

 

You should remember that the insert statement (in the previous example) adds a line with the delivdate value of September 6. However, the value is now September 3, three days earlier than the original one.

Use the datediff Method

 

The datediff method can calculate the time interval between two dates and return an integer representing the interval. This method uses the following syntax:

 

Datediff (<date/time_part>, <start_date>, <end_date>)

 

<Date/time_part> A placeholder refers to the part to be compared between two dates. For example, you want to confirm the hours or days between the start date and the end date.

 

Except for the workday (DW, W) abbreviation, the <date/time_part> placeholder uses the same abbreviation as the dateadd method. Datediff does not support workday comparison.

 

<Start_date> placeholders refer to the comparison start date, while <end_date> placeholders refer to the end date. In other words, the method returns the specific time or date interval between the start date and the end date.

 

Let's give an example to illustrate how it works. The following SELECT statement calculates the interval between orderdate and delivdate values in the sales. Orders table:

 

Select orderdate, delivdate, Datediff (DD, orderdate, delivdate) as daysdiff From sales. Orders Where orderid = 1002.

 

In this statement, datediff is used in the select list. The first parameter of this method specifies that the interval must be the number of days (dd), while the second parameter specifies orderdate as the start date, and the third parameter specifies delivdate as the end date. Therefore, datediff calculates the number of days between orderdate and delivdate. In this example, it is 7 days, as shown in the following results:

 

Orderdate	Delivdate	Daysdiff
2008-08-27 13:40:22. 357	2008-09-03 13:40:22. 357	7

 

Of course, it can also be used to calculate various time intervals, as shown in the following example:

 

Select orderdate, delivdate, Datediff (HH, orderdate, delivdate) as hoursdiff From sales. Orders Where orderid = 1002.

 

In this case, the first parameter of the method is hour (HH) rather than day. Therefore, the method returns the hours of difference between orderdate and delivdate, as shown in the following results:

 

Orderdate	Delivdate	Hoursdiff
2008-08-27 13:40:22. 357	2008-09-03 13:40:22. 357	168

 

The difference between the two values is 168 hours.

 

Like the dateadd method, the datediff method is not only used in select statements. For example, datediff can be used in the WHERE clause of the update statement to determine which row needs to be updated. In the following example, I used datediff to specify the rows with less than 8 days between orderdate and delivdate values.

 

Update sales. Orders Set delivdate = dateadd (DD, 3, delivdate) Where datediff (DD, orderdate, delivdate) <8 Go Select orderid, orderdate, delivdate, Datediff (DD, orderdate, delivdate) as daysdiff From sales. Orders

 

In the previous example, the datediff method returns the number of days between orderdate and delivdate. Then the number is compared with 8. If the number of days is less than 8, the row will be updated; otherwise, the row will not change. For the rows to be updated, I use the dateadd method to add three days to the delivdate value. Then, I run a SELECT statement to return the data in the sales. Orders table and calculate the two dates in each row, as shown in the following results:

 

Orderid	Orderdate	Delivdate	Daysdiff
1001	2008-08-27 13:36:16. 280	2008-09-08 18:27:10. 750	12
1002	2008-08-27 13:40:22. 357	2008-09-06 13:40:22. 357	10

 

The results show that there is a difference of 10 days between the two dates (in the second row), rather than the original 7 days.

 

Use dateadd and datediff in Table Definitions

 

The dateadd and datediff methods can also be used in table definitions. For example, the default clause of the field definition can use the dateadd method or the datediff method to create a calculated field. In the following Transact-SQL code, I first created a table using dateadd and datediff, then added a row of data to the table, and finally retrieved the table data:

 

Use adventureworks Go If exists (select table_name From information_schema.tables Where table_schema = 'sales' And table_name = 'Orders ') Drop table sales. Orders Go Create Table sales. Orders ( Orderid int not null, Orderdate datetime not null default getdate (), Delivdate datetime not null default dateadd (DD, 10, getdate ()), Daysdiff as datediff (DD, orderdate, delivdate) ) Go Insert into sales. Orders (orderid) Values (1, 1001) Go Select orderid, orderdate, delivdate, daysdiff From sales. Orders

 

In the create table statement, I created four fields, three of which store date/time data. The orderdate field uses getdate to generate the default value. The delivdate field also has a default value. However, this default value is based on the results returned by dateadd. In this case, I use the method to add 10 days to store the values returned by getdate TO THE delivdate field. Finally, the daysdiff field is a calculated field whose value is the number of days difference between orderdate and delivdate values calculated using datadiff.

 

After the table is defined, I insert a data row to the table. Because all date/time values are automatically generated, I just need to insert the orderid value, as shown below:

 

Orderid	Orderdate	Delivdate	Daysdiff
1001	2008-08-27 13:42:50. 433	2008-09-06 13:42:50. 433	10

 

The dateadd and datediff methods are not only useful in Table definitions, but also suitable for queries and data modification statements. Through dateadd, we can increase or decrease the date/time value. Through datediff, we can calculate the time interval between the date/time values. For more details about these methods, read Microsoft SQL Server books online.