Prev: Capture, log and parse all access - how?
Next: Tx log restore problems
From: sahinceylan on 14 Jul 2010 17:35
Hi,

I have a stored procedure which accept a datetime input parameter.

spMyProcedure '2010-07-14 13:25:48'

Above statement works fine in my development environment. But in the
server it gives 'varchar to datetime conversion error'. It treats '14'
as month and '07' as day.

I can resolve this issue by changing the input parameters type to
nvrachar and explicitly convert to date time
or by adding 'SET DATEFORMAT ymd' statement.

But i have a lot of stored procedures like this and i don't want to
change all of them.

Can you suggest any other solution.

Thanks.
From: Erland Sommarskog on 14 Jul 2010 17:49
sahinceylan (sahinceylan2001(a)gmail.com) writes:
> I have a stored procedure which accept a datetime input parameter.
>
> spMyProcedure '2010-07-14 13:25:48'
>
> Above statement works fine in my development environment. But in the
> server it gives 'varchar to datetime conversion error'. It treats '14'
> as month and '07' as day.
>
> I can resolve this issue by changing the input parameters type to
> nvrachar and explicitly convert to date time
> or by adding 'SET DATEFORMAT ymd' statement.
>
> But i have a lot of stored procedures like this and i don't want to
> change all of them.
>
> Can you suggest any other solution.

If you use date literals in SQL Server, you should use YYYYMMDD or
YYYY-MM-DDThh:mm:ss, where T stands for itself. These format are
safe, that is they don't depend on dateformat setting.

If these are calls from a client, you are calling your stored procedures
in the wrong way. You should *not* send EXEC statements, you should use
RPC. There are two reasons for this:

1) When you build EXEC strings, and interleave user input, you open
yourself for SQL injection.

2) You get the mess with datetime parameters that you have just run
into.

Since you don't say which client API you are using, I can't say how
you should do to implement RPC calls, but all client API I know,
supports RPC.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: sahinceylan on 15 Jul 2010 12:46
Thanks;
I used YYYY-MM-DDThh:mm:ss format, and everything works fine.
 | 
Pages: 1
Prev: Capture, log and parse all access - how?
Next: Tx log restore problems