7397e7ce   
   From: esquel@sommarskog.se   
      
   sahinceylan (sahinceylan2001@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@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)