Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value - SqlClient error

Отвечено 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
     
     
    Sign In to Vote
    0

    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.CommandType =

    CommandType.StoredProcedure;
     dbCommand.CommandText = "SPN_WF_GET_MY_APPOINTMENTS";
     dalManager.AddParameter(dbCommand,

    "@IV_USER_NAME", strUserName, StoredProcedureParameterDirection.Input, 30, DbType.String);

     dalManager.AddParameter(dbCommand,

    "@ID_FROM_DATE", dtFromDate, StoredProcedureParameterDirection.Input, DbType.DateTime);

    if (dtToDate.HasValue)
     {

    dalManager.AddParameter(dbCommand,

    "@ID_TO_DATE", dtToDate, StoredProcedureParameterDirection.Input, DbType.DateTime);

     }

    if (!string.IsNullOrEmpty(strPriority))

     {

    dalManager.AddParameter(dbCommand,

    "@IV_PRIORITY", strPriority, StoredProcedureParameterDirection.Input, 1, DbType.String);
     }

    dbDataAdapter.SelectCommand = dbCommand;

    DataTable dtMyAppointments = new DataTable();

     dbDataAdapter.Fill(dtMyAppointments);

    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.

    Thanks,

    Vandana

     

     

     

All Replies

  • Wednesday, November 02, 2011 8:34 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Vandana,


    ‘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.

    Best Regards,
    Iric
    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 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hello,

    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.

    http://msdn.microsoft.com/en-us/library/ms187819.aspx

    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

    http://msdn.microsoft.com/en-us/library/system.data.common.dbparameter_properties.aspx

    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 :

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx

    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.