Error while taking date between two dates

Question

I am trying to get date between two dates but i get error selection query is

DateTime startdate = Convert.ToDateTime(metroLabel8.Text);
DateTime enddate = Convert.ToDateTime(metroLabel9.Text);
SqlCommand cmd = new SqlCommand("Select [LedId],[LedName] from [Ledger] where Date>='"+startdate+"' and Date<='"+enddate+"'", con);

error is

Show the values in the textboxes, which produces this error ! Also use command parameters — mybirthname, Nov 29 '16 at 11:23
Use parameterized SQL - that may well be all you need. Never put values directly into SQL like this. — Jon Skeet, Nov 29 '16 at 11:24
I'm not sure, but take a look at this question. Hope it helps - http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates — , Nov 29 '16 at 11:25
It probably thinks it's got the day and month the wrong way round. Ideally, you'd be best converting to a date from this format yyyy-mm-dd — McGaz, Nov 29 '16 at 11:25
Possible duplicate of [Error sql:The conversion of a varchar data type to a datetime data type resulted in an out-of-range value](http://stackoverflow.com/questions/23722215/error-sqlthe-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted) — Neeraj Kumar, Nov 29 '16 at 11:26
did you check that your start date was not greater than endate? — farrukh aziz, Nov 29 '16 at 11:39

Dmitry Bychenko · Accepted Answer · 2016-11-29T11:34:37.567

Please, do not hardcode, use parametrized queries instead

   DateTime startdate = Convert.ToDateTime(metroLabel8.Text);
   DateTime enddate = Convert.ToDateTime(metroLabel9.Text);
   ...
   // Make sql readable
   string sql = 
     @"Select [LedId],
              [LedName] 
         from [Ledger] 
        where Date >= @prmStartDate and Date <= @prmEndDate";

   // wrap IDisposable (SqlCommand) into using
   using (SqlCommand cmd = new SqlCommand(sql, con)) {
     cmd.Parameters.Add("@prmStartDate", SqlDbType.DateTime).Value = startDate; 
     cmd.Parameters.Add("@prmEndDate", SqlDbType.DateTime).Value = endDate;

     ... 
   }

Hardcoded queries are

Prone to Sql injection
Brittle (depends on, say, datetime formatting - the gap you fell into)
Slow: there's performance decrease since RDBMS has to parse the query each time it executes it

score 0 · Answer 2 · answered Nov 29 '16 at 11:28

The best way to handle conversion is to let the provider handle that for you:

DateTime startdate = Convert.ToDateTime(metroLabel8.Text);
DateTime enddate = Convert.ToDateTime(metroLabel9.Text);
SqlCommand cmd = new SqlCommand("Select [LedId],[LedName] from [Ledger] where Date >= @startDate and Date <= @endDate", con);


SqlParameter startParameter = cmd.Parameters.Add("@startDate ",
                System.Data.SqlDbType.DateTime);
SqlParameter endParameter = cmd.Parameters.Add("@endDate",
                System.Data.SqlDbType.DateTime);

startParameter.Value = startdate;
endParameter.Value = enddate;

cmd.Parameters.Add(startParameter);
cmd.Parameters.Add(endParameter);

Don not concatenate strings when building up your SQL queries, this is prone to SQL injection and is considered a security issue in your code.

Ste Pammenter · Answer 3 · 2016-11-29T13:34:15.467

Without seeing your inputs, I would suggest you use parameterized SQL in your C# or create a stored procedure to accept DATETIME parameters. You shouldn't use hardcoded SQL queries - in short: they are prone to attack and not optmized in SQL.

A really easy way to do this would be to use the Dapper.NET object mapper.

In SQL, you could do:

    CREATE PROCEDURE return_led_for_dates
        @startdate      DATETIME,
        @enddate        DATETIME
    AS
    BEGIN
      SELECT
          [LedId],
          [LedName]
      FROM 
          [Ledger] 
      WHERE
          Date BETWEEN @Startdate AND @Enddate
   END

And with Dapper, your C# could then be:

  DateTime startdate = Convert.ToDateTime(metroLabel8.Text);
  DateTime enddate = Convert.ToDateTime(metroLabel9.Text);
  var LED = this.Connection.Query<LED>(
            "return_led_for_dates",
            new { 
               StartDate = startdate,
               EndDate = enddate 
             },
            commandType: CommandType.StoredProcedure);

You would need an LED class too:

    public class LED
    {
         int LedId {get; set;},
         string LedName {get; set;}
    }

Finally, this assumes that there is no issue with your text field conversions, you should use DateTime.TryParse. You can then bullet proof your code, and ensure the field is corretly parsed. Like:

    DateTime startDate;
    DateTime endDate;

    if (DateTime.TryParse(Convert.ToDateTime(metroLabel8.Text), out startDate) && DateTime.TryParse(Convert.ToDateTime(metroLabel9.Text), out endDate))
    {
       // Your data code.
    }

Error while taking date between two dates

3 Answers3