SM (SM@discussions.microsoft.com) writes:
[quoted text, click to view] > First of all sorry for posting incomplete or confusing query. Actually I
> am passing parameter as string to the Middle layer in my application.
> Because this layer is generic hence I cannot explicitly type cast the
> values. Hence I take the value passed through UI as it is & then fire it
> at the DB level. Now when I fire the mentioned query with date value as
> '2007.03.01. 11:20:20' then SQL Server throws an error. The error is of
> type casting, stating converion of varchar value to datetime resulted in
> an over flow. And it is understandeable that the last dot (.) in the
> date value is confusing SQL Server. But why SQL Server does not
> understand this date format? And even if it does not, is there an
> alternate or a provision for achieving this?
As I said, best practice is to pass dates as binary values, and you should
seriously reconsider your design. A generic middle layer that passes SQL
statements with embedded parameters values causes several problems, and
this is one of the smallest.
A more serious issue is performance: if you use parameterised queries, the
query plans can be reused. With embedded parameters values, you get a new
cache entry for little change in the query string. This can have a serious
impact on the performance on the system.
An even more seroius issue is security. Have you heard about SQL injection?
This is a technique whereby intruder can enter text that results in
commands being executed that you did not intend to. For instance instead
of a date, the entry reads:
2007 ' DROP TABLE important --
If this fits in syntactically, you have just lost a table if permissions
permit.
In essence, you have a broken design, and you need to repair that. I guess
you can address the particular problem with the date format by adding code
to convert the date to a format that is good for SQL Server, that is
YYYY-MM-DDThh:mm.ss. SQL Server does not look at regional settings, but
has its own setting for date formats. For instance try:
SET DATEFORMAT dmy
SELECT convert(datetime, '1999-12-23')
This will fail.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
