How do I use a textbox for a date query?

Question

How do I use a textbox for a date query?

Navigation

#1 by (2 votes)
#2 by (2 votes)

0

I'm trying to work in a query to find results by date range [from - to], so I use a textbox so that enter these values and put them in dd / mm / yyyy format with javascript, my problem is that when I give it to look for it, it marks me the following message;

MSJ ERROR: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

BUTTON

<asp:TextBox ID="txtDate1"  runat="server" ReadOnly="false"></asp:TextBox>
<asp:TextBox ID="txtDate2"  runat="server" ReadOnly="false"></asp:TextBox>

CONSULTATION

 SQLTable = "SELECT  * FROM ErrorLog ";
 SQLTable += "WHERE Date between '" + txtDate1.Text + "'";
 SQLTable += " AND '" + txtDate2.Text + "'";

asp.net webforms

asked by KJSK 12.09.2018 в 22:28

source

2 answers

2

It is not clear to me whether txtDate2 is a variable or the ID of TextBox ; but, you should do the following:

Validate that the TextBox with ID txtDate2 is not empty.
Validate that the TextBox value with ID txtDate2 is of date type.

Example:

if (!string.IsNullOrEmpty(txtDate2.Text.Trim())) 
{
   DateTime fecha = DateTime.Today;

   if (DateTime.TryParse(txtDate2.Text, out fecha))
   {
       // Convertir la fecha.
       fecha = DateTime.Parse(txtDate2.Text);

       // Aplique aquí el resto de su código.
   }
   else
   {
       // El valor del campo "txtDate2" no es una fecha.
   }
}
else
{
    // El campo "txtDate2" está vacío.
}

answered by 12.09.2018 в 22:36

array without repeated numbers How to change the name of the cookie that the token creates in the forms?

score 2 · Accepted Answer

The format to consult dates in SQL Server is yyyy-MM-dd for this reason is that it gives you the error using the format dd/MM/yyyy . The solution to your case would be to do this:

 SQLTable = "SELECT  * FROM ErrorLog ";
 SQLTable += "WHERE Date between '" + DateTime.ParseExact(txtDate1.Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd") + "'";
 SQLTable += " AND '" + DateTime.ParseExact(txtDate2.Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd") + "'";

Or failing that, change your format to the TextBox to yyyy-MM-dd .