Problem: This query in Toad for SQL Server 8.0 when using trim (BOTH ... keyword:
SELECT s.hour ,
CONVERT(DATETIME2,'01/01/1990 ' + trim(BOTH '"' from s.hour),101) dt1,
CONVERT (DATETIME2,'01/02/1990', 101) dt2
FROM dbo.calendar s
throws this error:
" Incorrect syntax near '"'. "
There is a workaround to this, by using this query:
SELECT s.hour ,
concat(CONVERT(DATETIME2,'01/01/1990 ',101) , trim( '"' from s.hour)) dt1,
CONVERT (DATETIME2,'01/02/1990', 101) dt2
FROM dbo.calendar s
Cause of the issue: There is a compatibility level that has to be met to use the trim (BOTH ... keyword, the database must be level 160, based on Microsoft's articles, anything lower will bring an error.
See the article here:
This means that by using SQL Server 2022 onwards, the BOTH keyword can be used.
Possible fix: upgrade Toad for SQL to use SQL Server 2022 engine