How to bind the SQL query datetime string literal with datetime instead of datetime2 with SNAC 11 driver in case of SQLBindParameter().

Question

While using the SNAC 11 driver for ODBC connection with our application, the query that is filtering on a datetime field and comparing it to a datetime value on the right of the comparison generates the SQL query with a program variable bound to the datetime2 datatype instead of datetime, which is not the case with the SQL Server driver. The following are the SQL statements observed using the SQL Server profiler for SNAC11 and the SQL Server driver, respectively:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 int,@P2 datetime2',N'SELECT "prrowid", "custnum", "country", "name", "address", "address2", "city", "state", "postalcode", "contact", "phone", "salesrep", "creditlimit", "balance", "terms", "discount", "comments", "fax", "emailaddress", "Pro2SrcPDB", "pro2created", "pro2modified" FROM "sports2000"."dbo"."customer" WHERE (("custnum" > @P1) AND ("pro2created" <= @P2)) ORDER BY "prrowid" ',5,'2023-08-10 14:39:55.5770000'
select @p1

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 int,@P2 datetime',N'SELECT "prrowid", "custnum", "country", "name", "address", "address2", "city", "state", "postalcode", "contact", "phone", "salesrep", "creditlimit", "balance", "terms", "discount", "comments", "fax", "emailaddress", "Pro2SrcPDB", "pro2created", "pro2modified" FROM "sports2000"."dbo"."customer" WHERE (("custnum" > @P1) AND ("pro2created" <= @P2)) ORDER BY "prrowid" ',5,'2023-08-10 15:03:34.400'
select @p1

Answer 1

Suggest re-asking here: SQL Server - Microsoft Q&A