Inconsistent dat paremeter in query

[Guest]

I am using the same code to query different databases, one SQL 2005 and the
other SQL 2000. The query contains a date paremeter @Date...

Me.SqlSelectCommand1.Parameters.AddRange(New
System.Data.SqlClient.SqlParameter() {New
System.Data.SqlClient.SqlParameter("@Date", System.Data.SqlDbType.DateTime,
8, "Date")})

The code was written using VS2003 (.Net 1.1) and is now executing on .Net 2.0.

SQL Profiler displays the generated query for SQL 2000 as...

exec sp_executesql N'SELECT FunctionID, Name, Date, Description FROM
vwTicketedFunctions WHERE (Date >= @Date) AND (OnSaleDate <= @Date) ORDER BY
StartTime', N'@Date datetime', @Date = 'Oct 26 2007 12:00:00:000AM'

and the generated query for SQL 2005 as ...

exec sp_executesql N'SELECT FunctionID, Name, Date, Description FROM
vwTicketedFunctions WHERE (Date >= @Date) AND
(OnSaleDate <= @Date) ORDER BY StartTime',N'@Date
datetime',@Date=''2007-10-26 00:00:00:000''

Note that the date paremeter formats are different. My problem is that the
SQL 2000 query parses Ok but the SQL 2005 generates the error: Line 2:
Incorrect syntax near '2007'.

Note also that regional setting is English(Australia).

 

[Cor Ligthert[MVP]]

Mike,

Net 1.1 and Net 2.0 are not completely compatible to its other, did you have
Net 1.1 as well installed?

Cor

 

[Guest]

Mike,

Net 1.1 and Net 2.0 are not completely compatible to its other, did you have
Net 1.1 as well installed?

Cor

No. Only .Net 2 is installed.

 

[Miha Markic]

Hi Mike

Your problem has nothing to do with .net. You get two different formatting
because probably you use two different sql profilers or two different
regional settings or something like that.
..net always send parameters as *data*, not text and thus the profiler output
format doesn't matter at all - that's one of the benefits of using
parametrised queries.

 

[Cor Ligthert[MVP]]

And what is the reason that it goes wrong?

At least I know that the way a parameter is added to the parametercollection
is changed between 1.1 and 2.0. If that is not the solution is possible,
however now you are so sure that it is not the problem, what is than the
reason?

Cor

 

[Miha Markic]

Because:
a) he is speaking about problems running the query (he got from profiler) in
sql client tool
b) one can't get invalid formatting of data when the value is passed as a
parameter
c) when using parametrisation the value, not formated text, is used

 

[Cor Ligthert[MVP]]

Yeh,

In my idea is the question is why there is a different when running on SQL
2005 with Net 2.0 a difference in the used values of the parameters instead
of SQL 2000 with Net 1.1.

@Date = 'Oct 26 2007 12:00:00:000AM'

@Date=''2007-10-26 00:00:00:000''

Cor

 

[Miha Markic]

This is a formatting issue of the sql client tool.
I don't know what tools OP is using and how or where are they used.
I wouldn't bother about it.