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,
Any help is greatly appreciated!!! Thanks.
The Stored Procedure code is as follows:
This procedure is invoked by the follwoing .NET code (ETA is a DateTime):
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.
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?
answered Jul 18 '12 at 12:21 PM
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!
answered Jul 18 '12 at 03:56 PM