How to use DATEDIFF() function in Sql Server

Posted By  RRaveen On 16 Dec 2010 10:12:48
emailbookmarkadd commentsprint
No of Views:4136
Bookmarked:0 times
Votes:0 times

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. If you are missing my previous tips, please click below links.

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

DatePartAbbreviations 
Yearyy,yyyy
Quarterq,qq
Monthmm,m
DayOfyeardy,y
Daydd,d
Weekwk,ww
WeekDaydw,w
Hourhh
Minutemi,n
Secondss,s
MilliSecondms

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

as like above sql script you can use other datepart to get the differences.

References

MSDN Article

Hopes help. thank for reading.if you have any comments please post end of the this tips.

Sign Up to vote for this article
Other popular Tips/Tricks
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top