Sunday, 19 February 2017

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
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
----------------------------------------------------------------------------------------------------

Happy Learning,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts