date difference function in sql with example program

•  datediff sql function will helps to calculate the difference between two dates in week,year,months etc.

• datediff sql function accept the three arguments start_date, end_date and date_part.

•  date_part is a part of the date like year months and week that's compare between the start_date and end_date.

• start_date and end_date are two dates which is going to be compare.They contain the value in the form of  type DATE, DATETIME, DATETIMEOFFSET, DATETIME2, SMALLATETIME, or TIME.

Table for date_part for datediff sql function

sql server datediff

Return value of sql server datediff function

SQL DATEDIFF() function will return the integer value to indicate the  difference between the start_date and end_date and specified by the date_part.

sql server datediff function return the error if range of the integer return value is (-2,147,483,648 to +2,147,483,647).

 Example for  datediff SQL function  for SQL Server

•  let see the differences between two date value 
  

Now use the DATEDIFF() function in SQL to compare two dates dates in various date parts:

DECLARE 
    @start_dt DATETIME2= '2019-12-31 23:59:59.9999999', 
    @end_dt DATETIME2= '2020-01-01 00:00:00.0000000';

SELECT 
    DATEDIFF(year, @start_dt, @end_dt) diff_in_year, 
    DATEDIFF(quarter, @start_dt, @end_dt) diff_in_quarter, 
    DATEDIFF(month, @start_dt, @end_dt) diff_in_month, 
    DATEDIFF(dayofyear, @start_dt, @end_dt) diff_in_dayofyear, 
    DATEDIFF(day, @start_dt, @end_dt) diff_in_day, 
    DATEDIFF(week, @start_dt, @end_dt) diff_in_week, 
    DATEDIFF(hour, @start_dt, @end_dt) diff_in_hour, 
    DATEDIFF(minute, @start_dt, @end_dt) diff_in_minute, 
    DATEDIFF(second, @start_dt, @end_dt) diff_in_second, 
    DATEDIFF(millisecond, @start_dt, @end_dt) diff_in_millisecond;

OUTPUT

DATEDIFF() function in SQL