How To Add Days, Months, Quarters and Years To DateTime in SQL Server?

How to add Weeks to DateTime in Sql Server?

How to add Months to DateTime in Sql Server?

How to add Quarters to DateTime in Sql Server?

How to add Years to DateTime in Sql Server?

1: How to add Days to DateTime in Sql Server?

Definition:-

The DATEADD() function adds or subtracts a specified time interval from a date.

Syntax –

DATEADD(DATEPART, NUMBER, DATETIME)

SELECT DATEADD(DAY, -(2), GETDATE()) AS DATEADD_DAYS

Result -

DATEADD_DAYS
-------------------------
2017-04-18 16:37:47.833

The date part will be,

a) Year

b) Quarter

c) Month

d) Dayofyear

e) Day

f) Week

g) Weekday

h) Hour

i) Minute

j) Second

k) millisecond

l) Microsecond

m) Manosecond etc.

Example as,

--INPUTS PARAMS
DECLARE @From_Date DATETIME = '2017-01-31'
DECLARE @UID BIGINT =19975

-- SELECT QUERY TO BIND BILING CHART.
SELECT PERIOD_DATE, 
	PERIOD, 
	SUM(COST) AS COST 
FROM UVW_BIL_BILL_SUMM
WHERE UID= @UID
	AND PERIOD_DATE >= DATEADD(DAY, -(2), @From_Date)
GROUP BY PERIOD_DATE, PERIOD --GROUP BY COULUMNS
ORDER BY PERIOD_DATE -- ORDER BY COULUMNS

2: How to add Weeks to DateTime in Sql Server?

Syntax - 

SELECT DATEADD(WEEK, -(2), GETDATE()) AS DATEADD_WEEK

Example as,

--INPUTS PARAMS
DECLARE @From_Date DATETIME = '2017-01-31'
DECLARE @UID BIGINT =19975

-- SELECT QUERY TO BIND BILING CHART.
SELECT PERIOD_DATE, 
	PERIOD, 
	SUM(COST) AS COST 
FROM UVW_BIL_BILL_SUMM
WHERE UID= @UID
	AND PERIOD_DATE >= DATEADD(WEEK, -(2), @From_Date)
GROUP BY PERIOD_DATE, PERIOD --GROUP BY COULUMNS
ORDER BY PERIOD_DATE -- ORDER BY COULUMNS

3: How to add Months to DateTime in Sql Server?

Syntax - 

SELECT DATEADD(MONTH, -(2), GETDATE()) AS DATEADD_MONTH

Example as,

--INPUTS PARAMS
DECLARE @From_Date DATETIME = '2017-01-31'
DECLARE @UID BIGINT =19975

-- SELECT QUERY TO BIND BILING CHART.
SELECT PERIOD_DATE, 
	PERIOD, 
	SUM(COST) AS COST 
FROM UVW_BIL_BILL_SUMM
WHERE UID= @UID
	AND PERIOD_DATE >= DATEADD(MONTH, -(2), @From_Date)
GROUP BY PERIOD_DATE, PERIOD --GROUP BY COULUMNS
ORDER BY PERIOD_DATE -- ORDER BY COULUMNS

4: How to add Quarters to DateTime in Sql Server?

Syntax - 

SELECT DATEADD(QUARTER, -(2), GETDATE()) AS DATEADD_QUARTER

Example as,

--INPUTS PARAMS
DECLARE @From_Date DATETIME = '2017-01-31'
DECLARE @UID BIGINT =19975

-- SELECT QUERY TO BIND BILING CHART.
SELECT PERIOD_DATE, 
	PERIOD, 
	SUM(COST) AS COST 
FROM UVW_BIL_BILL_SUMM
WHERE UID= @UID
	AND PERIOD_DATE >= DATEADD(QUARTER, -(2), @From_Date)
GROUP BY PERIOD_DATE, PERIOD --GROUP BY COULUMNS
ORDER BY PERIOD_DATE -- ORDER BY COULUMNS

5: How to add Years to DateTime in Sql Server?

Syntax - 

SELECT DATEADD(YEAR, -(2), GETDATE()) AS DATEADD_YEAR

Example as,

--INPUTS PARAMS
DECLARE @From_Date DATETIME = '2017-01-31'
DECLARE @UID BIGINT =19975

-- SELECT QUERY TO BIND BILING CHART.
SELECT PERIOD_DATE, 
	PERIOD, 
	SUM(COST) AS COST 
FROM UVW_BIL_BILL_SUMM
WHERE UID= @UID
	AND PERIOD_DATE >= DATEADD(YEAR, -(2), @From_Date)
GROUP BY PERIOD_DATE, PERIOD --GROUP BY COULUMNS
ORDER BY PERIOD_DATE -- ORDER BY COULUMNS

How to add Days, Weeks, Months, Quarters and Years to DateTime in Sql Server?

Stayed Informed – Learn SQL Server with Best Examples

Stayed Informed – Learn MongoDB with Best Examples

I hope you are enjoying with this post! Please share with you friends. Thank you!!

Pin it

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2