Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Create plan guide with date time parameters

Create plan guide with date time parameters

Create plan guide with date time parameters

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 ?



RE: Create plan guide with date time parameters

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.


RE: Create plan guide with date time parameters

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

CODE -->


@params = @P1 DATETIME2 


RE: Create plan guide with date time parameters

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close