Avoiding Date Format errors while writing SQL Queries

Many times we face the task of displaying records falling between two dates. We ask the user to enter the date range. The date format for user is dd/MM/yyyy (in India).

First assign the dates from the Text Box to some variables of Date type

  Dim tmpDateFrom As Date
  Dim tmpDateTo As Date
  Try
     tmpDateFrom = Date.ParseExact(txtDateFrom.Text, "dd/MM/yyyy",                           Globalization.CultureInfo.InvariantCulture)
     tmpDateTo = Date.ParseExact(txtDateTo.Text, "dd/MM/yyyy",                               Globalization.CultureInfo.InvariantCulture)
  Catch ex As Exception
     lblErrorMessage.Text = "Enter Date in dd/MM/yyyy format only!"
     pnlError.Visible = True
     Exit Sub
  End Try

When we need to insert the above dates in SQL query use the following syntax:

"WHERE (CONVERT(date, DOJ)>='" & Format(tmpDateFrom, "yyyy-MM-dd") & "' AND CONVERT(date, DOJ) <='" & Format(tmpDateTo, "yyyy-MM-dd") & "')"


When using parameters, use the following syntax:
 cmdInsert.Parameters.Add("@DateFrom", SqlDbType.Date)
 cmdInsert.Parameters("@DateFrom").Value = tmpDateFrom
 cmdInsert.Parameters.Add("@DateTo", SqlDbType.Date)
 cmdInsert.Parameters("@DateTo").Value = tmpDateTo