To quote MSDN:

DATEDIFF
Returns the number of date and time boundaries crossed between two specified
dates.

Syntax
DATEDIFF ( datepart , startdate , enddate )

Arguments
datepart

Is the parameter that specifies on which part of the date to calculate the
difference. The table lists dateparts and abbreviations recognized by
Microsoft® SQL Server?.

Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms


startdate

Is the beginning date for the calculation. startdate is an expression that
returns a datetime or smalldatetime value, or a character string in a date
format.

Because smalldatetime is accurate only to the minute, when a smalldatetime
value is used, seconds and milliseconds are always 0.

If you specify only the last two digits of the year, values less than or
equal to the last two digits of the value of the two digit year cutoff
configuration option are in the same century as the cutoff year. Values
greater than the last two digits of the value of this option are in the
century that precedes the cutoff year. For example, if the two digit year
cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted
as 1950. To avoid ambiguity, use four-digit years.

For more information about specifying time values, see Time Formats. For
more information about specifying dates, see datetime and smalldatetime.

enddate

Is the ending date for the calculation. enddate is an expression that
returns a datetime or smalldatetime value, or a character string in a date
format.

Return Types
integer

Remarks
startdate is subtracted from enddate. If startdate is later than enddate, a
negative value is returned.

DATEDIFF produces an error if the result is out of range for integer values.
For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and
23.647 seconds. For seconds, the maximum number is 68 years.

The method of counting crossed boundaries such as minutes, seconds, and
milliseconds makes the result given by DATEDIFF consistent across all data
types. The result is a signed integer value equal to the number of datepart
boundaries crossed between the first and second date. For example, the
number of weeks between Sunday, January 4, and Sunday, January 11, is 1.

Examples
This example determines the difference in days between the current date and
the publication date for titles in the pubs database.

USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO


See Also

CAST and CONVERT

Data Types

Date and Time Functions

©1988-2000 Microsoft Corporation. All Rights Reserved.

-----Original Message-----
From: The Book [mailto:The__Book@H...]
Sent: Saturday, June 02, 2001 3:24 AM
To: sql language
Subject: [sql_language] Query for Records Created Withing Last Time
Frame


I have a Users Table with 2 columns

Name     CreationTime

Henry    2001-06-01 18:15:30.100
Mary     2001-05-01 01:10:10.200
Betty    2001-06-01 17:30:25.300

What is the query that would return to me only those records that were
created within the last two hours, given that the current time is
18:20:10.100?


Thanks in advance.

TheBook