DATEDIFF (Transact-SQL)

Datadiff is used to get datasets within a specified time interval, such as:

                SelectCount (1) ascol2 fromComplaintmain cmwhereDateDiff (dd,cm. Createdon,getdate ())<=7and cm. Handlingby="+ (int) HANDLINGBY.MEMBERCD + @"and cm. Cstate="+ (int) complaintstate.successfully +";";

The above query is to get the condition data for one week;

Function method:

DATEDIFF (DatePart, StartDate, EndDate)

DatePart Parameters:

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
Microsecond	MCs
Nanosecond	Ns

Demo

SELECT DATEDIFF (year,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF (Quarter,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF (Month,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF (DayOfYear,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF (Day,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF (Week,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF (Hour,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF (minute,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF (Second,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF (Millisecond,'2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Example:

CREATE TABLE dbo. Duration    (    startdate datetime2    , endDate datetime2    ); INSERT into dbo. Duration (startdate,enddate)    VALUES ('2007-05-06 12:10:09','  2007-05-07 12:10:09'Duration'fromdbo. Duration; 1

DATEDIFF (Transact-SQL)