Monday, June 4, 2007

SQL DATEADD Function

Returns a new datetime value based on adding an interval to the specified date.

SQL DATEADD Syntax
DATEADD ( datepart , number, date )


DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(Year, 3, @DateNow) AS NewDate

Return Value = 2010-06-04 00:00:00.000


SELECT DATEADD(quarter, 3, @DateNow) AS NewDate
Return Value = 2008-03-04 00:00:00.000


SELECT DATEADD(Month, 3, @DateNow) AS NewDate
Return Value = 2007-09-04 00:00:00.000


SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Week, 3, @DateNow) AS NewDate
Return Value = 2007-06-25 00:00:00.000


SELECT DATEADD(Hour, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 03:00:00.000


SELECT DATEADD(minute, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:03:00.000


SELECT DATEADD(second, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:03.000


SELECT DATEADD(millisecond, 3, @DateNow) AS NewDate

Return Value = 2007-06-04 00:00:00.003

11 comments:

snarifeen said...
This comment has been removed by the author.
aashish said...

i want to calculate the number of working days in a particular month using SQL in microsoft access.please help me with the query.thanx

yasin kızılırmak said...

i am glad for your article.

subashree said...
This comment has been removed by the author.
subashree said...

What is the difference b/w the dateofyear and day type in the dateadd function? Following Queries return the same result. Can you tell the difference between them ?

SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000

Nishar said...

this is blog this is really very useful...!

Dharmit Doshi said...

Thank you so much for posting such syntaxes...
Keep Going On

Dharmit Doshi said...

i want to ask same question what subashree asked
i do have the same confusion

weirdo said...

SQL Reports
http://www.sql-reports.net/

Mohan said...

DATEADD(dayofyear,3, @DateNow)

i want to change the value of different (3) dynamically anybody help me

optisol biz said...

Great info on the website. Really enjoyed reading SQL Data add function info. Keep up the good work!

Hire SSRS developers