SQL Server DATEADD and DATEDIFF functions

SQL Server DATEADD and DATEDIFF :

DATEADD and DATEDIFF:

The best way to modify date and time values is by using the DATEADD and DATEDIFF functions. The DATEADD function can be used to add or subtract an interval to part of a date.

SELECT  DATEADD(hh, 5, GETDATE()) ;

SELECT  DATEADD(hh, -5, GETDATE()) ;

SELECT  DATEADD(dd, 1, GETDATE()) ;

Listing 1: Modifying dates

The DATEDIFF function can be used to calculate the difference between to dates. DATEDIFF is similar to DATEADD - DATEDIFF gets the difference between two dates using the given time interval (year, months, seconds, and so on).

SELECT  DATEDIFF(dd, '2015-04-21', GETDATE()) ;

SELECT  DATEDIFF(hh, '2015-04-21 09:37:00', '2015-04-21 17:54:25') ;

Listing 2: Finding the difference between two points in time

One practical use of the DATEDIFF function is to find the beginning of the current day or month.

SELECT  DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS beginning_of_day ;

SELECT  DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

                                                AS beginning_of_month ; 

Listing 3: Beginning of the day or month