Create plan guide with date time parameters

[niall5098]

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

 

[Chris Miller]

I would like to see the full udf declaration, but from what i can see I'd judge you need

(((T8.TRANSDATE>={ts @P1})

@params = @P1 DATETIME2

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

 

[Chris Miller]

I actually think even the {ts datestring} syntax isn't necessary, if the param already is a datetime.

(((T8.TRANSDATE>=@P1)

@params = @P1 DATETIME2

Chriss

 

[Chris Miller]

And last not least I learned {ts datestring} is ODBC/JDBC syntax or escape sequence. The way you use it it will arrive as is in SQL Server and you're actually lucky SQL Server can also understand this syntax, but if the ODBC layer should be fed with that escape sequence, it's counterproductive if the parameter can only be put into that seqence after it arrived in SQL Server.

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