title: SQL Server stored procedure: datetime parameter is wrong format sometimes depending on dateformat of country
body: <p>This SQL Server stored procedure takes several parameters, and one of them is a <code>datetime</code> data type. </p>
  
  <p>We've just began to localize the application in Europe, and the problem is that they're receiving this:</p>
  
  <blockquote>
    <p>Error converting data type nvarchar to datetime.</p>
  </blockquote>
  
  <p>I've done my reading on <code>datetime</code>, so I believe what is happening is the application is posting the date as dd/mm/yyyy to SQL Server, but since the database has a default language of English and expects mm/dd/yyyy, there is an error.</p>
  
  <p>Sample input that produces error: </p>
  
  <pre><code>@LastUpdatedDate = N'21.01.2016 03:54:08'
  </code></pre>
  
  <p>My coworker in Europe said that when he set the default language of the database to German, the error quit occurring. </p>
  
  <p>This error has not shown up in the USA/Canada, and I'm assuming that if the application <strong>AND</strong> SQL Server are running the same default <code>datetime</code> format, then no issues. However, we (here in USA), need to work with their application and database in Europe, so now we get the error. </p>
  
  <p>Here's the stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[addUser]
  (
      @UserID NVARCHAR ( 50 ),
      @AccountID NVARCHAR( 50 ) = '00000000000000000000000000000000',
      @Password NVARCHAR( 50 ),
      @FirstName nvarchar(50),
      @LastName nvarchar(50),
      @Telephone nvarchar(25),
      @Mobile nvarchar(25),
      @FAX nvarchar(25),
      @EmailAddress nvarchar(50),
      @CurrentUserID nvarchar(50),
      @LastUpdatedBy nvarchar(50),
      @LastUpdatedDate datetime 
  )
  AS
  BEGIN TRANSACTION
      INSERT INTO tbUsers (UserID, AccountID, Password, Status, VPID, EvalStatusID, FirstName, LastName, Telephone, Mobile, FAX, EmailAddress, LastUpdatedBy, LastUpdatedDate)
      VALUES (@UserID, @AccountID, @Password, 'A', 'PM', 'ACTIVE', @FirstName, @LastName, @Telephone, @Mobile, @FAX, @EmailAddress, @LastUpdatedBy, @LastUpdatedDate)
  </code></pre>
  
  <p>I don't have access to the application code yet, but question is:</p>
  
  <p>Is there a way to catch the error and fix the <code>datetime</code> input value depending on how database is configured? </p>
  
  <p>I tried using <code>SET DATEFORMAT dmy</code> before the <code>BEGIN TRANSACTION</code>, but it errors out before that. </p>
  
  <p>Yes, we could probably alter the application code to use a language-neutral <code>datetime</code> format, but that may not be feasible. </p>