Working with SQL Server Date/Time Variables: Part Four - Date Math and Universal Time - Page 2
June 3, 2003
The DATEDIFF function is used to calculate the number of date and time boundaries crossed between two different dates. This function returns an integer value. A call to the DATEDIFF function takes the following format:
DATEDIFF(datepart, startdate, enddate)
Where datepart is one of the following: year, quarter, month, dayofyear, day, hour, minute, second or millisecond. Startdate and enddate are datetime values for which you want to find the difference.
As stated above, the DATEDIFF function is used to calculate the number of date and time boundaries crossed between two dates. So what value do you think might be returned if you used '12/31/2002 23:59:59.997' for the startdate and '01/01/2003 00:00:00.000' for the enddate, when the datepart is day or year? Clearly, the amount of time between these two dates is only 3 milliseconds. Find out by running the following two SELECT statements.
What did you discover? You should have found, that the number of days difference is 1, and the number of years difference is also 1.
Now if you really want to calculate the number of milliseconds between each of these dates then you will need to use the millisecond datapart as the first parameter to the DATEDIFF function, like so:
Say you want to calculate the number of weeks between two dates, then you would use the following code:
This code returns 1, as the number of weeks between these two dates. Note that using the "SET FIRSTDAY" command does not change the results of using the WEEK datepart. The DATEDIFF function always assumes Sunday is the first day of a week. Now if you run the following, then there would be 0 weeks between these two dates. Since "2003-05-25" is a Sunday, and "2003-05-31" is a Saturday the DATEDIFF function returns a 0, since both theses dates are in the same week.
A more practical example of how an application might use the DATEDIFF function might be determining the number of days it takes to process an order. The number of days to process an order is calculated by determining the number of days between the date when an order was received and when the actual order was shipped out. Say we have the following ORDERS table, where the ORDER_DT is when the order was received, and the ORDER_SHIP_DT is the actual date when the order was shipped.
Now, to calculate the number of days to process an order with the DATEDIFF function, your SELECT statement would look like this:
When this statement runs against the above ORDERS table, the following output would be displayed.
Note from this output that your management might want to review why every time WIDGIT A was ordered it took 9 days to process the order, although all the rest of the WIDGITS (B, X, and Z) only took 1 day to process the order.