x

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:

ALTER PROCEDURE [dbo].[VRS_UpdateETA]
@index int,
@eta datetime
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 UPDATE dbo.VMVoyagePort 
 set 
 ETA = @eta,
 where 
 ID = @index
END

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

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

public void ExecuteProcWithParams(string procName, params DbParameter[] parameters)
        {
            try
            {
                using (DbCommand cmd = db.CreateCommand())
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = procName;
                    cmd.CommandType = CommandType.StoredProcedure;

                    foreach (DbParameter param in parameters)
                    {
                        cmd.Parameters.Add(param);
                    }

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

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);        
                    break;
                default:
                    throw new Exception(String.Format("Unknown database type {0}", databaseType));
            }

            return result;
        }
more ▼

asked Jul 18 '12 at 11:11 AM in Default

mylo40 gravatar image

mylo40
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 '12 at 12:26 PM Kev Riley ♦♦
Thats useful :-)
Jul 18 '12 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 '12 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

SET DATEFORMAT MDY
--OR
--SET LANGUAGE English

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 '12 at 11:31 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

BTW, Language and DateFormat are not properties of the database. They can be set for the session OR User etc.
Jul 18 '12 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 '12 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 '12 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?

Thanks!
more ▼

answered Jul 18 '12 at 12:21 PM

mylo40 gravatar image

mylo40
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 '12 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 '12 at 12:51 PM mylo40
It should end with .mmm not :mmm. Please try this and revert.
Jul 18 '12 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 '12 at 01:01 PM mylo40
Actually it results in this error : Error converting data type nvarchar to datetime."
Jul 18 '12 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 '12 at 03:56 PM

mylo40 gravatar image

mylo40
0 1 1 1

Triggers are evil.....
Jul 18 '12 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 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x91
x14
x12

asked: Jul 18 '12 at 11:11 AM

Seen: 1468 times

Last Updated: Jul 19 '12 at 06:13 AM