SQL Server : DATEADD() not working beyond milliseconds when using a string value-CodePudding

Does anyone know why something like this:

SELECT DATEADD(HOUR,4,'2021-11-01 05:10:00.0000000')

returns the following error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

But when using only up to milliseconds like

SELECT DATEADD(HOUR, 4, '2021-11-01 05:10:00.000')

it works and returns:

2021-11-01 13:10:00.000

And when using it in a table where the column includes the nanosecond like

SELECT DATEADD(HOUR, 4, column-name)

it also works.

CodePudding user response：

Because you've supplied a literal string, and so DATEADD converts the value to it's default data type, datetime. A datetime is accurate to 1/300th of a second and a value like 2021-11-01 05:10:00.0000000 therefore is too large; it has an accuracy greater than 1/300th of a second.

If you explicitly converted the value to a date and time data type that goes up to 1/1000000 of a second, it would work:

SELECT DATEADD(HOUR,4,CONVERT(datetime2(7),'2021-11-01 05:10:00.0000000'))

CodePudding user response：

From the dateadd documentation:

A string literal value must resolve to a datetime.

The datetime type can only accept milliseconds. Attempts to parse a string that has more accuracy than milliseconds will fail.

(Note that the datetime is rounded .000, .003, or .007 seconds. For example, 1 ms is rounded down to 0.)

The same error occurs if you try to declare a datetime and initialize it to a literal string that has more than 3 decimal places of second accuracy:

DECLARE @datetime datetime = '12:10:05.0000'

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

You can add to a datetime2, but you must declare the datetime2 variable first instead of using a string literal:

declare @date datetime2 = '2021-11-01 05:10:00.0000000'
SELECT DATEADD(HOUR, 4, @date)

2021-11-01 09:10:00.0000000