IntroductionThis 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. If you are missing my previous tips, please click below links. DATEDIFF() SyntaxDATEDIFF(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 |
ExamplesDECLARE @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
as like above sql script you can use other datepart to get the differences. ReferencesMSDN Article Hopes help. thank for reading.if you have any comments please post end of the this tips. |