压延扩展器选择日期格式向DateTime数据类型的转换导致值超出范围 [英] The conversion of a calender extender selection date format to a DateTime data type resulted in an out-of-range value

问题描述

我试图通过将压延扩展剂选择输入文本框并从数据库中获取相应的计数来获取特定日期的记录数。 checkdate列的数据类型为DateTime。我们尝试过：

I am trying to fetch number of records for a particular date by feeding the calender extender selection into a textbox and fetching the corresponding count from the database. The datatype of the checkdate column is DateTime. We tried :

protected void Page_Load(object sender, EventArgs e) {
  if (!IsPostBack) {
    string result = "select count(*) from <TableName> where Checkdate= GETDATE() and sub_code=@sub_code";
    SqlCommand cmd = new SqlCommand(result, connection);
    connection.Open();
    Label3.Visible = true;
    Label3.Text = cmd.ExecuteScalar().ToString();
    connection.Close();
  }
}

protected void Button1_Click(object sender, EventArgs e) {
  Label5.Visible = true;

  Label3.Visible = true;

  string query = "select count(*) from <TableName> where Checkdate= @checkdate and sub_code=@sub_code";

  SqlCommand cmd = new SqlCommand(query, connection);
  cmd.Parameters.AddWithValue("@checkdate", tbdate.Text);
  connection.Open();

  Label5.Text = cmd.ExecuteScalar().ToString();

  connection.Close();
}

但是出现以下错误：
nvarchar的转换数据类型转换为日期时间数据类型导致值超出范围。
说明：当前Web请求的执行期间发生未处理的异常。请查看堆栈跟踪以获取有关错误及其在代码中起源的更多信息。

But I get the Following error : The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

异常详细信息：System.Data.SqlClient.SqlException：将nvarchar数据类型转换为日期时间数据类型会导致超出范围的值。

Exception Details: System.Data.SqlClient.SqlException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

源错误：

第125行：connection.Open（）;
第126行：
第127行：Label5.Text = cmd.ExecuteScalar（）。ToString（）;
第128行：
第129行：connection.Close（）;

Line 125: connection.Open(); Line 126: Line 127: Label5.Text = cmd.ExecuteScalar().ToString(); Line 128: Line 129: connection.Close();

存储在数据库中的日期格式为：2018-04- 24 12：00：22.803

The format of the date stored in DB is as :2018-04-24 12:00:22.803

推荐答案

我设法通过以下方法解决了该问题：我只保存了get的日期部分date as：checkdate = convert（date，GETDATE（））
，然后像这样调用它：

I managed to resolve the issue by the following approach where I just save the date part of get date as : checkdate= convert(date, GETDATE()) and then call it like this :

  SqlCommand cmd = new SqlCommand(query, connection); 
  string textboxdate = tbdate.Text;
            DateTime lastdate = DateTime.ParseExact(textboxdate,
                       "dd/MM/yyyy",
                       System.Globalization.CultureInfo.InvariantCulture);

            string newFormat = lastdate.ToString("yyyy-MM-dd");
            cmd.Parameters.AddWithValue("@checkdate", newFormat);

           connection.Open();


            Label5.Text = cmd.ExecuteScalar().ToString();

            connection.Close();

这篇关于压延扩展器选择日期格式向DateTime数据类型的转换导致值超出范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！