How to Modify Date and Time Values with DateDiff and DateAdd Functions in SQL Server

SQL Server DateDiff and DateAdd Functions to Modify Date and Time Values

USE TAMATAM_db GO

Declare @StDate as DATE = '06/14/2015' --'MM/DD/YYYY HH:MM:SS'

Declare @EnDate as DATE='12/25/2017'

Declare @NewDate as DATE

---------------------------------------------------------

Select DateDiff(YY,@StDate,@EnDate ) AS DateDiff_in_Years

--Results the Ouput as '2'; The Difference of Dates in Years

Select DateDiff(QQ,@StDate,@EnDate ) AS DateDiff_in_Quarter

--Results the Ouput as '10'; The Difference of Dates in Quarters

Select DateDiff(MM,@StDate,@EnDate ) AS DateDiff_in_Months

--Results the Ouput as '30'; The Difference of Dates in Months

Select DateDiff(WK,@StDate,@EnDate ) AS DateDiff_in_Weeks

--Results the Ouput as '132'; The Difference of Dates in Weeks

Select DateDiff(DD,@StDate,@EnDate) AS DateDiff_in_Days

--Results the Ouput as '925'; The Difference of Dates in Days
---------------------------------------------------------

SELECT DATEADD(YY,2,@StDate) AS Add_Years_to_Date

--Results as '2017-06-14'; The Addition of 2 Years to '06/14/2015'

SELECT DATEADD(YY,-2,@StDate) AS Substract_Years_from_Date

--Results as '2013-06-14'; The Substraction of 2 Years from '06/14/2015'

---------------------------------------------------------

SELECT DATEADD(QQ,2,@StDate) AS Add_Quaters_to_Date

--Results as '2015-12-14'; The Sub Addition of 2 Quarters from '06/14/2015'

SELECT DATEADD(QQ,-2,@StDate) AS Substract_Quaters_from_Date

--Results as '2014-12-14'; The Sub Straction of 2 Quarters to '06/14/2015'

---------------------------------------------------------

SELECT DATEADD(MM,4,@StDate) AS Add_Months_to_Date

--Results as '2015-10-14'; The Addition of 4 Months to '06/14/2015'

SELECT DATEADD(MM,-4,@StDate) AS Substract_Months_from_Date

--Results as '2015-02-14'; The Substraction of 4 Months from '06/14/2015'

---------------------------------------------------------

SELECT DATEADD(WW,4,@StDate) AS Add_Weeks_to_Date

--Results as '2015-07-12'; The Addition of 4 Weeks to '06/14/2015'

SELECT DATEADD(WW,-4,@StDate) AS Substract_Weeks_from_Date

--Results as '2015-05-17'; The Substraction of 4 Weeks from '06/14/2015'

---------------------------------------------------------

SELECT DATEADD(DD,17,@StDate) AS Add_Days_to_Date

--Results as '2015-07-01'; The Addition of 17 Days to '06/14/2015'

SELECT DATEADD(DD,-14,@StDate) AS Substract_Days_from_Date

--Results as '2015-05-31'; The Substraction of 14 Days from '06/14/2015'

---------------------------------------------------------

SELECT Day(@StDate) AS Day_of_Month

--Results as '14'; The Day of the Month in the Date '06/14/2015'

SELECT Day(EOMONTH(@StDate)) AS Days_in_Month

--Results as '30'; The last Day of the Month in the Date '06/14/2015'
GO

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------