Does anyone know why when using something like SELECT DATEADD(HOUR,4,'2021-11-01 05:10:00.0000000') it returns 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：

I believe it is a formatting error. I believe the length of digits is wrong so sql is seeing it as a regular varchar instead of a date. This website may be useful. https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/