The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value - SqlClient error
Monday, October 31, 2011 12:57 PM
I am executing a stored procedure which accepts date time parameters. Following is the pience of the code that i have written
// Get the data adapter
DbDataAdapter dbDataAdapter = dalManager.GetAdapter();
// Get the data command
DbCommand dbCommand = dalManager.GetCommand();
DbConnection conn = dalManager.GetConnection();
dbCommand.Connection = conn;
dbCommand.CommandText = "SPN_WF_GET_MY_APPOINTMENTS";
"@IV_USER_NAME", strUserName, StoredProcedureParameterDirection.Input, 30, DbType.String);
"@ID_FROM_DATE", dtFromDate, StoredProcedureParameterDirection.Input, DbType.DateTime);
"@ID_TO_DATE", dtToDate, StoredProcedureParameterDirection.Input, DbType.DateTime);
"@IV_PRIORITY", strPriority, StoredProcedureParameterDirection.Input, 1, DbType.String);
dbDataAdapter.SelectCommand = dbCommand;
DataTable dtMyAppointments = new DataTable();
I am query a stored procedure here with two date parameters and following is the profiler script of SQL Server
exec SPN_GET_MY_APPOINTMENTS @IV_USER_NAME=N'ABC',@ID_FROM_DATE='Oct 31 2011 12:00:00:000AM',@ID_TO_DATE='Nov 4 2011 12:00:00:000AM'
When i run this script generated from the profiler directly on the SQL Server it returns me a correct resultset with no errors. However the exception "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value" occurs when I try to fill the dataadapter i.e. when i do a dataAdapter.Fill(dtMyAppointments).
Also I have this piece of code on several enviroments however this seems to happen only on 2 of the 6 enviroments. I am not sure why this is happening as the stored procedure does not not seem to have any issues with the date format?
I am not sure if I am missing anything. Any help on this is highly appreciated.
Wednesday, November 02, 2011 8:34 AMModerator
‘Oct 31 2011 12:00:00:000AM’ is an invalid value for cell.
Please see: http://msdn.microsoft.com/en-us/library/ms141005.aspx
And change the value valid.
Hope this helps.
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Wednesday, November 02, 2011 1:31 PMModerator
Iric is right when he/she wrote that the date is ( maybe ) not correct.
When i want to use a stored procedure with parameters as DateTime, i am always using the iso-format ( iso 8601 )You are sure that it works with any collation for Windows or SQL Server and you will be understood by everybody.
You may have a problem with the definition of your parameters.Please,could you post the text of your stored procedure ?
A little remark about your code : i am not using your way to create/add a new parameter. I prefer to write more code but it is clearer and it is easier to detect any error.
DbParameter _dbparm = new DbParameter();
_dbparm.DbType = DbType.DateTime;
... and i am loading the properties of _dbparm with the needed properties from
i am using the Value to load the value of the parameter.
and i finish with dbCommand.Parameters.Add(_dbparm)
Please, could you tell us why you are using DbCommand instead the equivalent for a specific driver ?
For example for SqlCommand :
Maybe, because you want to be independant of the driver ?
A little remark : to post code you should use the Insert code button ( just at the right of the button labelled HTML ).Don't forget to specify the language (VC#,SQL).You will have a more readable code and it is easier to do a copy/paste from a code block.I have not used it because i am not using my own computer, so i have posted the code from my remembrance .
Don't hesitate to post again for more explanations or help
Have a nice day
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.