DATEDIFF() function in Sql Server

Introduction

This is the third tips date related function in sql server. In this I going to show you how to use DATEDIFF() function in sql server. The DATEDIFF () is useful to get the specified date part between two dates

DATEDIFF() Syntax

DATEDIFF(datepart,Startdate,EndDate)

datepart: Is the parameter that specifies on which part of the date to return a new value

startdate: Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.

enddate: same as like startdate.

 List of support abbreviation for datepart

DatePart	Abbreviations
Year	yy,yyyy
Quarter	q,qq
Month	mm,m
DayOfyear	dy,y
Day	dd,d
Week	wk,ww
WeekDay	dw,w
Hour	hh
Minute	mi,n
Second	ss,s
MilliSecond	ms

Examples

DECLARE @startdate datetime

declare @enddate datetime

set @startdate=DATEADD(MONTH,-1,getdate());

set @enddate=GETDATE();

SELECT DATEDIFF(Year, @startdate, @enddate) AS DiffYears -- get no of years different between two dates

------------Output-----------------------------

----------------------------------------------

-- 0 Years

SELECT DATEDIFF(QUARTER, @startdate, @enddate) AS DiffYears -- get no of quarters different between two dates

------------Output-----------------------------

----------------------------------------------

-- 0 quarters

SELECT DATEDIFF(MOnth, @startdate, @enddate) AS DiffYears -- get no of months different between two dates

------------Output-----------------------------

----------------------------------------------

-- 1 Months

SELECT DATEDIFF(week, @startdate, @enddate) AS DiffYears -- get no of weeks different between two dates

------------Output-----------------------------

----------------------------------------------

-- 4 weeks

SELECT DATEDIFF(day, @startdate, @enddate) AS DiffYears -- get no of days different between two dates

------------Output-----------------------------

----------------------------------------------

-- 30 days

SELECT DATEDIFF(hour, @startdate, @enddate) AS DiffYears -- get no of hours different between two dates

------------Output-----------------------------

----------------------------------------------

-- 720 hours

SELECT DATEDIFF(MINUTE, @startdate, @enddate) AS DiffYears -- get no of minutes different between two dates

------------Output-----------------------------

----------------------------------------------

-- 43200 minutes

SELECT DATEDIFF(SECOND, @startdate, @enddate) AS DiffYears -- get no of seconds different between two dates

------------Output-----------------------------

----------------------------------------------

-- 2592000 seconds

Have a nice day... 'N happy Coding :)