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.
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'))