The DATEDIFF() function is used to returns the time between two given dates. Let’s say you want to know the Day difference between two dates, Second difference, millisecond difference then you can use SQL server standard built in function DATEDIFF(). In easy way DATEDIFF() gives us Date Diffenrence between two dates.
DATEDIFF() SQL Syntax:
For datepart CLICK HERE, based on datepart DATEDIFF() function returns day or time or quarter between two given date (start date & end Date).
DATEDIFF() SQL Function Example:
In the below example i will show how you can get hour, day, month, year difference between 2 given dates:
SELECT DATEDIFF(hh,GETDATE(),GETDATE()+65) [Hour Gap], DATEDIFF(day,GETDATE(),GETDATE()+65) [Day Gap], DATEDIFF(month,GETDATE(),GETDATE()+65) [Month Gap], DATEDIFF(year,GETDATE(),GETDATE()+65) [Year Gap]
Here i want to discuss a bit more on SQL Time Difference because most of the forum i found the question how we can get time difference in SQL for the same date.
Query to get difference in Minute, Query to get difference in Second, Query to get difference in Millisecond:
SELECT DATEDIFF(mi, '2009-01-03 22:40:37.00', '2009-01-03 22:40:45.33') Minute SELECT DATEDIFF(ss, '2009-01-03 22:40:37.00', '2009-01-03 22:40:45.33') Second SELECT DATEDIFF(ms, '2009-01-03 22:40:37.00', '2009-01-03 22:40:45.33') MiliSecond
Another interesting example is age calculation. Using DATEDIFF() built in function you can easily calculate personels age up to current date or up to any other date from stored Date of birth data. Though the below example is not the standard way to calculate age but to understand the DATEDIFF function the below example will help you. Here is an example how you can calculate:
-- Change '1979-01-01' by your age column name -- Change GETDATE() if you don't want to consider system date SELECT DATEDIFF(year,'1979-01-01',GETDATE()) [Age]