2024年6月12日发(作者:)
SELECT DATEADD(year,2147483648, '2006-07-31');
SELECT DATEADD(year,-2147483649, '2006-07-31');
Copy Code
date Argument
The date argument cannot be incremented to a value outside the range of its data type. In the following statements, the number value that is
added to the date value exceeds the range of the date data type. The following error message is returned: "Adding a value to a 'datetime'
column caused overflow."
Copy Code
SELECT DATEADD(year,2147483647, '2006-07-31');
SELECT DATEADD(year,-2147483647, '2006-07-31');
Return Values for a smalldatetime date and a second or Fractional Seconds datepart
The seconds part of a smalldatetimevalue is always 00. If date is smalldatetime, the following apply:
If datepart is second and number is between -30 and +29, no addition is performed.
If datepart is second and number is less than-30 or more than +29, addition is performed beginning at one minute.
If datepart is millisecond and number is between -30001 and +29998, no addition is performed.
If datepart is millisecond and number is less than -30001 or more than +29998, addition is performed beginning at one minute.
Remarks
DATEADD can be used in the SELECT , WHERE, HAVING, GROUP BY and ORDER BY clauses.
Fractional Seconds Precision
Addition for a datepart of microsecond or nanosecond for date data types smalldatetime, date, and datetime is not allowed. Milliseconds
have a scale of 3 (.123). microseconds have a scale of 6 (.123456). nanoseconds have a scale of 9 (.123456789). The time, datetime2, and
datetimeoffset data types have a maximum scale of 7 (.1234567). If datepart is nanosecond, number must be 100 before the fractional
seconds of date increase. A number between 1 and 49 is rounded down to 0 and a number from 50 to 99 is rounded up to 100. The
following statements add a datepart of millisecond, microsecond, or nanosecond.
Copy Code
DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111'
SELECT '1 millisecond' ,DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT '2 milliseconds', DATEADD(millisecond,2,@datetime2)
UNION ALL
SELECT '1 microsecond', DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT '2 microseconds', DATEADD(microsecond,2,@datetime2)
UNION ALL
SELECT '49 nanoseconds', DATEADD(nanosecond,49,@datetime2)
UNION ALL
SELECT '50 nanoseconds', DATEADD(nanosecond,50,@datetime2)
UNION ALL
SELECT '150 nanoseconds', DATEADD(nanosecond,150,@datetime2);
/*
Returns:
1 millisecond 2007-01-01 13:10:10.1121111
2 milliseconds 2007-01-01 13:10:10.1131111
1 microsecond 2007-01-01 13:10:10.1111121
2 microseconds 2007-01-01 13:10:10.1111131
49 nanoseconds 2007-01-01 13:10:10.1111111
50 nanoseconds 2007-01-01 13:10:10.1111112
150 nanoseconds 2007-01-01 13:10:10.1111113
*/
Time Zone Offset
Addition is not allowed for time zone offset.
Examples
A. Incrementing datepart by an interval of 1
Each of the following statements increments datepart by an interval of 1.
Copy Code
DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111'
SELECT 'year', DATEADD(year,1,@datetime2)
UNION ALL
SELECT 'quarter',DATEADD(quarter,1,@datetime2)
UNION ALL
SELECT 'month',DATEADD(month,1,@datetime2)
UNION ALL
SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime2)
UNION ALL
SELECT 'day',DATEADD(day,1,@datetime2)
UNION ALL
SELECT 'week',DATEADD(week,1,@datetime2)
UNION ALL
SELECT 'weekday',DATEADD(weekday,1,@datetime2)
UNION ALL
SELECT 'hour',DATEADD(hour,1,@datetime2)
UNION ALL
SELECT 'minute',DATEADD(minute,1,@datetime2)
UNION ALL
SELECT 'second',DATEADD(second,1,@datetime2)
UNION ALL
SELECT 'millisecond',DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT 'microsecond',DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT 'nanosecond',DATEADD(nanosecond,1,@datetime2);
/*
Year 2008-01-01 13:10:10.1111111
quarter 2007-04-01 13:10:10.1111111
month 2007-02-01 13:10:10.1111111
dayofyear 2007-01-02 13:10:10.1111111
day 2007-01-02 13:10:10.1111111
week 2007-01-08 13:10:10.1111111
weekday 2007-01-02 13:10:10.1111111
hour 2007-01-01 14:10:10.1111111
minute 2007-01-01 13:11:10.1111111
second 2007-01-01 13:10:11.1111111
millisecond 2007-01-01 13:10:10.1121111
microsecond 2007-01-01 13:10:10.1111121
nanosecond 2007-01-01 13:10:10.1111111
*/
B. Incrementing more than one level of datepart in one statement
Each of the following statements increments datepart by a number large enough to also increment the next higher datepart of date.
Copy Code
DECLARE @datetime2 datetime2;
SET @datetime2 = '2007-01-01 01:01:01.1111111';
--Statement Result
-------------------------------------------------------------------
SELECT DATEADD(quarter,4,@datetime2); --2008-01-01 01:01:01.110
SELECT DATEADD(month,13,@datetime2); --2008-02-01 01:01:01.110
SELECT DATEADD(dayofyear,365,@datetime2); --2008-01-01 01:01:01.110
SELECT DATEADD(day,365,@datetime2); --2008-01-01 01:01:01.110
SELECT DATEADD(week,5,@datetime2); --2007-02-05 01:01:01.110
SELECT DATEADD(weekday,31,@datetime2); --2007-02-01 01:01:01.110
SELECT DATEADD(hour,23,@datetime2); --2007-01-02 00:01:01.110
SELECT DATEADD(minute,59,@datetime2); --2007-01-01 02:00:01.110
SELECT DATEADD(second,59,@datetime2); --2007-01-01 01:02:00.110
SELECT DATEADD(millisecond,1,@datetime2); --2007-01-01 01:01:01.110
C. Using expressions as arguments for the number and date parameters
The following examples use different types of expressions as arguments for the number and date parameters.
Specifying column as date
The following example adds
2
days to each
OrderDate
to calculate a new
PromisedShipDate
.
Copy Code
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID
,OrderDate
,DATEADD(day,2,OrderDate) AS PromisedShipDate
FROM rderHeader;
Specifying user-defined variables as number and date
The following example specifies user-defined variables as arguments for number and date.
Copy Code
DECLARE @days int;
DECLARE @datetime datetime;
SET @days = 365;
SET @datetime = '2000-01-01 01:01:01.111'; /* 2000 was a leap year */
SELECT DATEADD(day, @days, @datetime);
Specifying scalar system function as date
The following example specifies
SYSDATETIME
for date.
Copy Code
SELECT DATEADD(month, 1, SYSDATETIME());
Specifying scalar subqueries and scalar functions as number and date
The following example uses scalar subqueries and scalar functions,
MAX(ModifiedDate)
, as arguments for number and date.
(SELECT TOP 1
BusinessEntityID FROM )
is an artificial argument for the number parameter to show how to select a number argument from a value
list.
Copy Code
USE AdventureWorks2008R2;
GO
SELECT DATEADD(month,(SELECT TOP 1 BusinessEntityID FROM ),
(SELECT MAX(ModifiedDate) FROM ));
Specifying constants as number and date
The following example uses numeric and character constants as arguments for number and date.
SELECT DATEADD(minute, 1, '
2007-05-07
09:53:01.0376635');
Specifying numeric expressions and scalar system functions as number and date
The following example uses a numeric expressions (-
(10/2))
, unary operators (
-
), an arithmetic operator (
/
), and scalar system functions
(
SYSDATETIME
) as arguments for number and date.
Copy Code
SELECT DATEADD(month,-(10/2), SYSDATETIME());
Specifying ranking functions as number
The following example uses a ranking function as arguments for number.
Copy Code
USE AdventureWorks2008R2;
GO
SELECT ame, me
,DATEADD(day,ROW_NUMBER() OVER (ORDER BY
Code),SYSDATETIME()) AS 'Row Number'
FROM erson AS s
INNER JOIN AS p
ON ssEntityID = ssEntityID
INNER JOIN s AS a
ON sID = ssEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
Specifying an aggregate window function as number
The following example uses an aggregate window function as an argument for number.
发布评论