String was not recognised as a valid datetime exception

Question

I am getting an above exception while converting datetime to varchar

if (columnDefs[i].IndexOf("Date") > -1)
{
    // Date search
    subQuery += columnDefs[i] + 
                " = '%" + 
                DateTime.Parse(searchValues[i]).ToString("dd/MM/yyyy") + 
                "'" + " and ";
}

Might want to include a language tag so it gets better coverage. — ForeverZer0, Aug 16 '18 at 06:31
It looks like you're building up an SQL query by string concatenation. **Stop** doing that. Pass your datetimes *as* datetimes by using parameters. — Damien_The_Unbeliever, Aug 16 '18 at 06:42
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Aug 16 '18 at 06:58

score 0 · Answer 1 · answered Aug 16 '18 at 07:06

When working with SQL do not hardcode but paramterize queries; assuming that you work with MS SQL which has @parameter_name syntax:

if (columnDefs[i].IndexOf("Date") >= 0)
{
    // Date search: we a parameter, not its value into the query
    subQuery += $" ({columnDefs[i]} = @prm_{columnDefs[i]}) and ";
}

...

// when executing the query
using (var myQuery = new SqlQuery(subQuery, myConnection)) {
  // we set parameters' values
  for (int i = 0; i < columnDefs.Count(); ++i) {
    if (columnDefs[i].IndexOf("Date") >= 0) {
      //TODO: check the actual parameter value type (SqlDbType.DateTime?) 
      var prm = new SqlParameter($"@prm_{columnDefs[i]}", SqlDbType.DateTime);
      //TODO: check actual string format (d/M/yyyy?)
      prm.Value = DateTime.ParseExact(
         searchValues[i], 
        "d/M/yyyy", 
         CutureInfo.InvariantCulture);

      myQuery.Parameters.Add(prm);
    }
  }

  ...
}

String was not recognised as a valid datetime exception

1 Answers1