I have migrated a SQL Server database from one server to another. I did this via export to BAK and then Restore on the new machine. Seems to be a different format somewhere, as a simple query that was working previously, is now throwing an error, but I cannot see what it might be (collation and 'containment' info seem the same).

The old SQL Server version: Microsoft SQL Server 2012 - 11.0.6598.0 Express Edition

The new SQL Server version: Microsoft SQL Server 2019 - 15.0.2080.9 Express Edition

The error, below, refers to a date format:

 SELECT userID FROM tblLogin 
 WHERE CAST('30/09/2021 00:52:14' AS datetime) < DATEADD(n,600,accessDate)

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

(Column accessDate is of type datetime, null)

CodePudding user response：

Always always ALWAYS use the ISO-8601 date formats for literals in Sql Server. For historical reasons, the preferred option for date-only values is different than date-time values, so you want either yyyy-MM-ddTHH:mm:ss for date and time or yyyyMMdd for date-only. But the really important thing is always go in order from most significant (year) to least significant (second).

Anything else isn't really a date literal at all: it's a string you must convert to a date.

If we follow this correct convention (because anything else really is incorrect), you get it down to this (which doesn't even need the CAST() anymore, because Sql Server can interpret it as datetime from the beginning):

SELECT userID 
FROM tblLogin 
WHERE accessDate > DATEADD(n, -600, '2021-09-30T00:52:14')

Also note I inverted the check, moving the DATEADD() function to act on the literal, instead of the column. This is always preferred, because it can work with any index you may have on the accessDate column. The original code would have rendered any such index worthless for this query.