DATEDIFF
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.
SELECT DATEDIFF(DAY,'20021231 23:59:59.997',
'20030101 00:00:00.000')
SELECT DATEDIFF(YEAR,'20021231 23:59:59.997',
'20030101 00:00:00.000')

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:
SELECT DATEDIFF(MS,'20021231 23:59:59.997',
'20030101 00:00:00.000')

Say you
want to calculate the number of weeks between two dates, then you would use the
following code:
SELECT DATEDIFF(WEEK,'20030524','20030525')

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 "20030525" is a Sunday, and "20030531" is a Saturday the DATEDIFF
function returns a 0, since both theses dates are in the same week.
SELECT DATEDIFF(WEEK,'20030525','20030531')

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.
ID

ORDER_DT

ORDER_SHIP_DT

ITEM_ORDERED

1

20030401

20030410

WIDGIT A

2

20030402

20030403

WIDGIT B

3

20030402

20030411

WIDGIT A

4

20030403

20030404

WIDGIT X

5

20030405

20030414

WIDGIT A

6

20030405

20030406

WIDGIT Z

7

20030407

20030408

WIDGIT B

Now, to
calculate the number of days to process an order with the DATEDIFF function,
your SELECT statement would look like this:
SELECT ITEM_ORDERED, DATEDIFF(DAY,ORDER_DT, ORDER_SHIP_DT) AS
PROCESSING_DAYS FROM ORDERS

When this
statement runs against the above ORDERS table, the following output would be
displayed.
ITEM_ORDERED 
PROCESSING_DAYS 
WIDGIT A 
9 
WIDGIT B 
1 
WIDGIT A 
9 
WIDGIT X 
1 
WIDGIT A 
9 
WIDGIT Z 
1 
WIDGIT B 
1 
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.