SQL DATEADD

2015-08-25 by suresh

SQL DATEADD function is used to add specific date interval to the existing date. For example, For every product order we will save the Ordered Date and we can use this SQL Dateadd function to generate the expected delivery date.

SQL DATEADD Syntax

1	DATEADD (Datepart, Number, Date)

Datepart: It is the part of a given data to which we are going add the Number argument. Following table will display the list of available datepart argument in SQL Server 2014

Datepart	Abbreviations	Description
year	yy,yyyy	This will add specified number of years (in second argument) to the given date
quarter	qq, q	This will add specified number of Quarters (in second argument) to the given date
month	mm, m	This will add specified number of Months (in second argument) to the given date
dayofyear	dy, y	This will add specified number of dayofyear (in second argument) to the given date
day	dd, d	This will add specified number of days (in second argument) to the given date
week	wk, ww	This will add specified number of Weeks (in second argument) to the given date
weekday	dw, w	This will add specified number of Weekdays (in second argument) to the given date
hour	hh	This will add specified number of Hours (in second argument) to the given date
minute	mi, n	This will add specified number of Minutes (in second argument) to the given date
second	ss, s	This will add specified number of Seconds (in second argument) to the given date
millisecond	ms	This will add specified number of Milliseconds (in second argument) to the given date
microsecond	mcs	This will add specified number of Microseconds (in second argument) to the given date
nanosecond	ns	This will add specified number of Nanoseconds (in second argument) to the given date

Date: Please specify the valid date as third argument and it can be column, expression or any variable.

Number: Please specify the number you want to add.

• If you specify the positive integer as the second argument (Number), SQL DATEADD function will add the specified number to the given date.
• If you specify the Negative integer as the second argument (Number), SQL DATEADD function will subtract the specified number from the given date.
• If you specify the Decimal number as the second argument (Number), SQL DATEADD function will truncate the decimal values and add specified number to the given date.

Positive integer in SQL DATEADD

In this example we are going to use the positive integer as a second argument

SQL CODE

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

DECLARE @DateAdd datetime2 = '2015-01-01 14:24:04.1234567' SELECT 'YEAR' AS [DatePart], DATEADD(year, 1, @DateAdd) AS [New Date] UNION ALL SELECT 'QUARTER', DATEADD(quarter,1,@DateAdd) UNION ALL SELECT 'MONTH', DATEADD(month, 1, @DateAdd) UNION ALL SELECT 'DAYOFYEAR', DATEADD(dayofyear, 1, @DateAdd) UNION ALL SELECT 'DAY', DATEADD(day, 1, @DateAdd) UNION ALL SELECT 'WEEK', DATEADD(week, 1, @DateAdd) UNION ALL SELECT 'WEEKDAY', DATEADD(weekday,1,@DateAdd) UNION ALL SELECT 'HOUR', DATEADD (hour, 1, @DateAdd) UNION ALL SELECT 'MINUTE', DATEADD(minute, 1, @DateAdd) UNION ALL SELECT 'SECOND', DATEADD(second, 16, @DateAdd) UNION ALL SELECT 'MILLISECOND', DATEADD(millisecond, 1, @DateAdd) UNION ALL SELECT 'MICROSECOND', DATEADD(microsecond, 50, @DateAdd) UNION ALL SELECT 'NANOSECOND',DATEADD(nanosecond,500,@DateAdd)

OUTPUT

ANALYSIS

We declared one variable and assigned the date and time to that variable

1	DECLARE @DateAdd datetime2 = '2015-01-01 14:24:04.1234567'

We asked the SQL Dateadd function to add 1 year to the given date

1	SELECT 'YEAR', DATEADD(year, 1, @DateAdd)

We asked the SQL Dateadd function to add 1 month to the given date

1	SELECT 'MONTH', DATEADD(month, 1, @DateAdd)

Negative Integer in SQL DATEADD

In this example we are going to use the Negative integer as a second argument

SQL CODE

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

DECLARE @DateAdd datetime2 = '2015-01-01 14:24:04.1234567' SELECT 'YEAR' AS [DatePart], DATEADD(year, -1, @DateAdd) AS [New Date] UNION ALL SELECT 'QUARTER', DATEADD(quarter, -1, @DateAdd) UNION ALL SELECT 'MONTH', DATEADD(month, -1, @DateAdd) UNION ALL SELECT 'DAYOFYEAR', DATEADD(dayofyear, -1, @DateAdd) UNION ALL SELECT 'DAY', DATEADD(day, -1, @DateAdd) UNION ALL SELECT 'WEEK', DATEADD(week, -1, @DateAdd) UNION ALL SELECT 'WEEKDAY', DATEADD(weekday, -1, @DateAdd) UNION ALL SELECT 'HOUR', DATEADD (hour, -1, @DateAdd) UNION ALL SELECT 'MINUTE', DATEADD(minute, -1, @DateAdd) UNION ALL SELECT 'SECOND', DATEADD(second, -14, @DateAdd)

OUTPUT

ANALYSIS

We asked the SQL Dateadd function to add 1 year to the given date but the function is subtracting 1 year from it. This is because Negative value is used to subtract

1	SELECT 'YEAR', DATEADD(year, -1, @DateAdd)

We asked the SQL Dateadd function to add 1 Month to the given date but the function is subtracting 1 Month from it. This is because Negative value is used to subtract

1	SELECT 'MONTH', DATEADD(month, -1, @DateAdd)

Decimal Values in SQL DATEADD

In this example we are going to use the decimal value as a second argument

SQL CODE

1 2 3 4 5 6

DECLARE @DateAdd datetime2 = '2015-01-01 14:24:04.1234567' SELECT 'YEAR' AS [DatePart], DATEADD(year, 2.23, @DateAdd) AS [New Date] UNION ALL SELECT 'QUARTER', DATEADD(quarter, 3.98, @DateAdd) UNION ALL SELECT 'MONTH', DATEADD(month, -4.51, @DateAdd)

OUTPUT

ANALYSIS

We asked the SQL Dateadd function to add 2.23 years to the given date but the function added 2 years only. This is because .23 values are truncated

1	SELECT 'YEAR', DATEADD(year, 2.23, @DateAdd)

We asked the SQL Dateadd function to add 4.51 months to the given date but the function subtracted 4 months from given date. This is because, Negative value is used to subtract and .51 value is truncated

1	SELECT 'MONTH', DATEADD(month, -4.51, @DateAdd)

Thank You for Visiting Our Blog