Monday, November 30, 2020

, , ,

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
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
DATEDIFF() function in SQL

0 comments:

Post a Comment