how to get date in dd/MM/yyyy format from textbox?

Question

I'm getting data between two date from query but problem is textbox getting data in YYYY/MM/dd format i need to convert it into dd/MM/yyyy.

Start Date: <asp:TextBox ID="txtstart" runat="server" TextMode="Date" ></asp:TextBox>
End Date: <asp:TextBox ID="txtend" runat="server" TextMode="Date"></asp:TextBox>
<p>
  <asp:Button ID="btnfetch" runat="server" Text="Button" OnClick="btnfetch_Click1" />
</p>
<div>
  <asp:GridView ID="GridView2" runat="server">
  </asp:GridView>
</div>

using (SqlCommand cmd = new SqlCommand(
  "SELECT Name,Present from AttendanceRecords " + 
  "where Date between '" + txtstart.Text + "' and '" + txtend.Text + "' ", con))
{
    con.Open();   
    SqlDataAdapter da = new SqlDataAdapter(cmd);

    DataTable dt = new DataTable();
    da.Fill(dt);
    SqlDataReader rdr = cmd.ExecuteReader();
    if (rdr.Read())
    {
        GridView2.DataSource = dt;
        GridView2.DataBind();
    }
    else
    {
        lbldatercrd.Text = "NOT FOUND";
    }

Hi Adil, please update your question to provide the code that renders your textbox. Are you using WebForms? Can you provide an example of your current output? — Jack, Oct 15 '18 at 17:10
Why not use a date picker? And also, what you have there is really open to injection... You should be parametrising your SQL, not concatenating the string like that. — Thom A, Oct 15 '18 at 17:10
Your code is vulnerable to SQL Injection attacks. Use parameters instead of concatenating strings. [Read here how it makes your query safe](https://stackoverflow.com/questions/8263371/how-can-prepared-statements-protect-from-sql-injection-attacks) Another benefit of using parameters is that you don't have to worry any more about the format of the string representation of date, you simple pass an instance of `DateTime` to the database. — Zohar Peled, Oct 15 '18 at 17:11
Ikf a textbox gets it in a form, it is because the user ENTERS is in that form. Change your backend thread language, properly handle multi language setups. — TomTom, Oct 15 '18 at 17:20
Always use parameterized sql and avoid string concatenation to add values to sql statements. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204), and [Exploits of a Mom](https://xkcd.com/327/). — Igor, Oct 15 '18 at 17:21
@Jack yes i am using webform.there is no output come bcz date in the database in dd/MM/yyyy while in textbox date is in YYYY/MM/dd format. — Adil Yar, Oct 15 '18 at 17:22
Dates have no format in SQL Server. Your dates don't have the format dd/MM/yyyy, they are simply the data type `date` (or `datetime`, `datetime2`, etc). If you are using a `varchar` to store dates in your database then you are doing it **wrong**. — Thom A, Oct 15 '18 at 17:24
`date in the database in dd/MM/yyyy` <= Always use the appropriate/matching types in the database. Sql-Server allows for types `Date`, `DateTime`, and `DateTime2` to represent dates. Use either `Date` for no time component *or* `DateTime2` if you want to persist time as well. — Igor, Oct 15 '18 at 17:24
@AdilYar yes, that's true, however, that information wasn't supplied when I, and others made their comments (and at the moment it's only stated in the comments). Why it's really important to provide all the information in the question. — Thom A, Oct 15 '18 at 17:51
@AdilYar There are third-party ones: [ASP.NET DateTime Picker](https://stackoverflow.com/questions/1469280/asp-net-datetime-picker). — Andrew Morton, Oct 15 '18 at 18:46

score 1 · Accepted Answer · answered Oct 15 '18 at 17:36

Adil,

You should always use parameters to prevent SQL injection attack,
It is expected that you should use a DateTime or DatePicker, TimePicker controls instead of textboxes but for the time being I would assume you are getting valid dates in the format of yyyy/MM/dd.
You shouldn't use BETWEEN for datetime range queries IF there is a possibility that the values would also have time portions. But here looks like you are only dealing with dates so that might be OK.

A DataAdapter opens and closes the connection as needed.

DateTime start, end;
if (DateTime.TryParse(txtStart.Text, out start) && DateTime.TryParse(txtEnd.Text, out end))
{
  string cmd = @"SELECT Name,Present 
      from AttendanceRecords   
      where [Date] between @start and @end";

  SqlDataAdapter da = new SqlDataAdapter(cmd, con);
  da.SelectCommand.Parameters.Add("@start", SqlDbType.DateTime).Value = start;
  da.SelectCommand.Parameters.Add("@end", SqlDbType.DateTime).Value = end;

  DataTable dt = new DataTable();
  da.Fill(dt);
  GridView2.DataSource = dt;
  GridView2.DataBind();
}
else
{
    lbldatercrd.Text = "NOT FOUND";
}

how to get date in dd/MM/yyyy format from textbox?

1 Answers1