Error calling MSSql Stored Proc from WebService with DateTime

I have a .Net WCFWebService that connects to an MSSQL database.

I am calling a stored procedure that takes an sql datetime value as a parameter which is passed from the web service application as a .NET DateTime value via a parameterised procedure call.

However, i am getting the following error and i cant work out what the issue is (am new to MSSQL!):-

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated."

Interestingly if i use the MsSQL profiler i can grab the executed SQL command and run it now problems from within Management Studio? It appears as:-

exec dbo.VRS_UpdatePositionAndDistance @voyageid=19020,@portid=115191,@eta='2012-07-18 11:54:00:000'

The database language is set to British,

 set language British
 set dateformat dmy

Any help is greatly appreciated!!! Thanks.

The Stored Procedure code is as follows:

 @index int,
 @eta datetime
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  UPDATE dbo.VMVoyagePort 
  ETA = @eta,
  ID = @index

This procedure is invoked by the follwoing .NET code (ETA is a DateTime):

                 dbWrapper.CreateParameter("@index", ),
                 dbWrapper.CreateParameter("@eta", ETA));

 public void ExecuteProcWithParams(string procName, params DbParameter[] parameters)
                 using (DbCommand cmd = db.CreateCommand())
                     cmd.Transaction = trans;
                     cmd.CommandText = procName;
                     cmd.CommandType = CommandType.StoredProcedure;
                     foreach (DbParameter param in parameters)
             catch (Exception)

 public DbParameter CreateParameter(string name, object value)
             DbParameter result = null;
             switch (databaseType)
                 case DatabaseType.SqlServer:
                     // Sql Server: NULL parameters have to initialised with DBNull rather than NULL
                     result = new SqlParameter(name, value ?? DBNull.Value);        
                     throw new Exception(String.Format("Unknown database type {0}", databaseType));
             return result;

more ▼

asked Jul 18, 2012 at 11:11 AM in Default

avatar image

0 1 1 1

The problem with grabbing the SQL from profiler, is that it assumes you are running with US defaults on the settings - hence the dates/times are often formatted incorrectly for any other region!

Jul 18, 2012 at 12:26 PM Kev Riley ♦♦

Thats useful :-)

Jul 18, 2012 at 12:28 PM mylo40

@Kev Riley Sir, I do not think so. If someone is sending a String literal then that will be shown as it is. And I am sure this is the case here as well.

Jul 18, 2012 at 12:39 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

There are quite a few solutions to the problem. If you want it to be handle it in your procedure (just for the application), then add the following at the start of the procedure


But that means you then have to pass the date according to MDY/English(US ENGLISH) format. Otherwise, you may still face the same error.

Now for the application part, we always tend to send the data with one UniversalFormats like YYYY-MM-DDThh:mm:ss.mmm. This way we do not face any conversion issues.

more ▼

answered Jul 18, 2012 at 11:31 AM

avatar image

Usman Butt
13.9k 6 12 21

BTW, Language and DateFormat are not properties of the database. They can be set for the session OR User etc.

Jul 18, 2012 at 11:39 AM Usman Butt

If i am passing the .Net DateTime as a parameter into the procedure how would i set the outuput to the Universal Format? I can see from the profiler that the format is 'yyyy-MM-dd HH:mm:ss' i.e

exec dbo.UpdateETA @id=19020,@eta='2012-07-21 11:45:00:000'

Jul 18, 2012 at 12:01 PM mylo40

You are missing 'T' in between the date and time parts. Its YYYY-MM-DDThh:mm:ss.mmm

Jul 18, 2012 at 12:04 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

But this works if i execute it in Management Studio: exec dbo.UpdateETA @id=19020,@eta='2012-07-21 11:45:00:000'

Yet if i execute the stored proc from my web service management studio reports the executed sql as above and the web service reports an exception indicating that 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value'?

I'm a bit lost as to what the difference is? Also if i want to format the .NET DateTime to ensure it gets converted to the ISO format how do i do it?


more ▼

answered Jul 18, 2012 at 12:21 PM

avatar image

0 1 1 1

It would work because of the user/session settings you have. I am pretty sure that would be us_english. For .NET, there is Format Function in which you can define custom formats

Jul 18, 2012 at 12:35 PM Usman Butt

If i try to format the DateTime using the string format with 'yyyy-MM-ddTHH:mm:ss:mmm' i get the same error. I can see in the profiler that the date time is formatted according to the custom format string and that it is now being passed as a string.

Jul 18, 2012 at 12:51 PM mylo40

It should end with .mmm not :mmm. Please try this and revert.

Jul 18, 2012 at 12:57 PM Usman Butt

Sorry usman i do in fact have it correct in my code so the error stands! Still confused as to why passing in a .NET DateTime doesn't result in the ISO formatting hitting the DB procedure? As i say i'm new to MSSql...not seen this issue in oracle :-)

Jul 18, 2012 at 01:01 PM mylo40

Actually it results in this error : Error converting data type nvarchar to datetime."

Jul 18, 2012 at 01:03 PM mylo40
(comments are locked)
10|1200 characters needed characters left

Oh !$%^*&(! Ok so i failed to notice a trigger that was taking my input value and doinf some validation etc. The error message returned made it look like the value i was supplying was at fault. It wasn't! Sorry and thanks for your help!

more ▼

answered Jul 18, 2012 at 03:56 PM

avatar image

0 1 1 1

Triggers are evil.....

Jul 18, 2012 at 03:57 PM Kev Riley ♦♦

Evil they are. But I still cannot understand why the execution was successful through SSMS and how the input was passed to the trigger :-????

Jul 19, 2012 at 06:13 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 18, 2012 at 11:11 AM

Seen: 2260 times

Last Updated: Jul 19, 2012 at 06:13 AM

Copyright 2016 Redgate Software. Privacy Policy