SQL Server - DATEADD() , DATEDIFF() Functions

DATEADD Function

Date math is an important part of working with SQL. Using the DATEADD function you can add or subtract a datepart (month, day, year, week, hour, minute, second) from a date. This function is useful up in the select or in the where clause.

This function returns the datetime resultant from the function. Below are some examples.

SELECT TOP 1
  --DATEADD    DATEADD (datepart , number , date )
  --DATE
   DATEADD (month , 3 , GETDATE() ) AS "Add 3 Months"
  ,DATEADD (month , -3 , GETDATE() ) AS "Subtract 3 Months"
  ,DATEADD (day , 7 , GETDATE() ) AS "Add 7 Days"
  ,DATEADD (week , 1 , GETDATE() ) AS "Add 1 Week"
  ,DATEADD (year , 1 , GETDATE() ) AS "Add 1 Year"
  --TIME      
  ,DATEADD (hour , 3 , GETDATE() ) AS "Add 2 Hours"
  ,DATEADD (minute , 30 , GETDATE() ) AS "Add 30 Minutes"
FROM AdventureWorks2008R2.Sales.Store

 

DATEDIFF Function

The datediff function differs from the dateadd function in the respect that you are comparing two datetime columns, or in the case of our example a hardcoded date.

The function also differe from dateadd in that it returns an integer value instead of a date.

SELECT TOP 1
  --DATEDIFF   DATEDIFF ( datepart , startdate , enddate )
   DATEDIFF(day, GETDATE(),    'Nov 23 1958') AS "DATEDIFF DAYS"
  ,DATEDIFF(month, GETDATE(),  'Nov 23 1958') AS "DATEDIFF MONTHS"
  ,DATEDIFF(year, GETDATE(),   'Nov 23 1958') AS "DATEDIFF YEARS"
  ,DATEDIFF(hour, GETDATE(),   'Nov 23 1958') AS "DATEDIFF HOURS"
  ,DATEDIFF(minute, GETDATE(), 'Nov 23 1958') AS "DATEDIFF MINUTES"
  ,DATEDIFF(second, GETDATE(), 'Nov 23 1958') AS "DATEDIFF SECONDS"
  ,DATEDIFF(month, ModifiedDate, GETDATE()) AS "ModifiedDate From Today"
FROM AdventureWorks2008R2.Sales.Store


Returns...

datediff

 

 

 
 Collapse All    Expand All
Custom Search

SQL Server Information and Resources