For a datepart of nanosecond, number must be 100 before the fractional seconds of date increase. The time, datetime2, and datetimeoffset data types have a maximum scale of 7 (.1234567). Milliseconds have a scale of 3 (.123), microseconds have a scale of 6 (.123456), and nanoseconds have a scale of 9 (.123456789). For a datepart of millisecond and a number value less than -30001, or more than +29998, DATEADD performs its addition beginning at one minute.ĭATEADD does not allow addition for a datepart of microsecond or nanosecond for date data types smalldatetime, date, and datetime.For a datepart of millisecond and a number value between -30001 and +29998, DATEADD makes no changes.For a datepart of second, and a number value less than -30, or more than +29, DATEADD performs its addition beginning at one minute.For a datepart of second, and a number value between -30 and +29, DATEADD makes no changes.For a smalldatetime date value, the following apply: The seconds part of a smalldatetime value is always 00. Return Values for a smalldatetime date and a second or Fractional Seconds datepart DATEADD returns the following error message: " Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow." SELECT DATEADD(year,2147483647, '20060731') In the following statements, the number value added to the date value exceeds the range of the date data type. SELECT DATEADD(year,-2147483649, '20060731') ĭATEADD will not accept a date argument incremented to a value outside the range of its data type. Arithmetic overflow error converting expression to data type int." SELECT DATEADD(year,2147483648, '20060731') These statements both return the following error message: " Msg 8115, Level 16, State 2, Line 1. In the following statements, the argument for number exceeds the range of int by 1.
The number argument cannot exceed the range of int. For example, September has 30 (thirty) days therefore, these statements return 00:00:00.000: SELECT DATEADD(month, 1, '20060830') Then, DATEADD returns the last day of the return month.
the date day does not exist in the return month.the date month has more days than the return month.Return Value datepart Argumentĭayofyear, day, and weekday return the same value.Įach datepart and its abbreviations return the same value. DATEADD raises an error if the string literal seconds scale exceeds three decimal place positions (.nnn) or if the string literal contains the time zone offset part. If another valid input data type is supplied for date, DATEADD returns the same data type. If the value for date is a string literal date, DATEADD returns a datetime value. The return type depends on the argument supplied for date. The return value data type for this method is dynamic. See Configure the two digit year cutoff Server Configuration Option for information about two-digit years. Use four-digit years to avoid ambiguity issues. A string literal value must resolve to a datetime. It will not round the number value in this situation.Īn expression that can resolve to one of the following values:įor date, DATEADD will accept a column expression, expression, string literal, or user-defined variable. DATEADD will truncate a specified number value that has a decimal fraction.
DATEADD accepts user-defined variable values for number. datepartĪn expression that can resolve to an int that DATEADD adds to a datepart of date. DATEADD does not accept user-defined variable equivalents for the datepart arguments.
0 kommentar(er)
