Create plan guide with date time parameters
Create plan guide with date time parameters
(OP)
HI There, i have a poorly performing query i want to create a plan guide for. the query includes the line:
(((T8.TRANSDATE>={ts '2022-10-16 00:00:00.000'}))
in my plan guide i changed the line to:
(((T8.TRANSDATE>={ts ''@P1''})
@params = N'@P1 DATETIME2
however when i try to create the plan guide i get the following error:
Msg 241, Level 16, State 3, Line 3
Conversion failed when converting date and/or time from character string.
any idea how can get around this ?
Regards,
Niall
(((T8.TRANSDATE>={ts '2022-10-16 00:00:00.000'}))
in my plan guide i changed the line to:
(((T8.TRANSDATE>={ts ''@P1''})
@params = N'@P1 DATETIME2
however when i try to create the plan guide i get the following error:
Msg 241, Level 16, State 3, Line 3
Conversion failed when converting date and/or time from character string.
any idea how can get around this ?
Regards,
Niall
RE: Create plan guide with date time parameters
CODE
Notice while you specify a dateitime as a single quoted string, that's just the source code form of a datetime, as it would be unfriendly to humans 8and programmers and IT managers) to sepcify datetimes in their binary form. The single quote string delimitere are just used because that gives a convenient delimiter and also, because indeed strings can be converted implicitly to datettimes, i.e. you don't need to write CONVERT('datestring',datetime), but 'datestirng' is implicitly converted to datetime.
If you define a parameter though, that can itself be defined as datetime already, and you skip that step. As you experienced yourself assuming dates are indeed strings, ou get the wrong idea about how to define parameters and commparisons now between incompatible data types.
Remember one thing: Programming does never mean WYSIWYG as that can be in text processors. As long as you have the wrong idea by lack of knowledge, you do wrong things. That you get an error pointing out a conversion problem should already have opend your eyes, indeed.
Chriss
RE: Create plan guide with date time parameters
CODE -->
Chriss
RE: Create plan guide with date time parameters
Again, know what you're doing. Tha last time iI specifically used ODBC syntax for anything was when I needed to address the ODBC driver from C. Any higher level language nowadays offers an ODBC interface in which you talk the SQL dialect of the target database, not the ODBC syntax. So I'd say you should better even forget about knowing the {ts datetiime} syntax.
I checked SQL Server indeed understands it, but it's completely unnecessary to wrap something in ODBC syntax. I don't think this is where your performance degrades, but the error shows it's not working with this intertwined quirky usage of ODBC and T-SQL language concepts. If a parameter @P1 is a datetime and the field you compare is a datetime, then the comparison needs nothing for conversion.
Chriss