c# - SQL data error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Question

I have a SQL query that's returning this error:

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

Here's the query :

POQuery = "SELECT Distinct PurchaseOrders.POrderID, PurchaseOrders.VendorName, PurchaseOrderDetail.ArrDate "
          + "FROM PurchaseOrders "
          + "FULL JOIN PurchaseOrderDetail ON PurchaseOrders.POrderID = PurchaseOrderDetail.POrderID "
          + "WHERE PurchaseOrders.Buyer = @Buyer and ArrDate >= convert(DateTime, '"
          + datePickerStart.Value.ToString("mm/dd/yyyy") + "') and ArrDate <= convert(DateTime, '"
          + datePickerEnd.Value.ToString("mm/dd/yyyy") + "')";

I'm using the datepicker to pick dates.

Answer 1

Your problem is culture related... You should never rely on culture specific date-time formats! Otherwise you'd have to tell the T-SQL CONVERT function the specific format (which should be 101 in your case)...

Just try this:

SET LANGUAGE ENGLISH;
SELECT CONVERT(datetime, '1/22/2016 9:14:44 AM'); --works
GO
SET LANGUAGE GERMAN;
SELECT CONVERT(datetime, '1/22/2016 9:14:44 AM'); --error
GO
SET LANGUAGE GERMAN;
SELECT CONVERT(datetime, '1/22/2016 9:14:44 AM',101); --works

Best is to use ISO8601 2016-01-22T18:59:00

or one of the ODBC formats which is

{d'2016-01-22'}
{t'18:59:00'}
{ts'2016-01-22 18:59:00'}

Doing so, you don't even have to call convert...

And - as others have pointed out - you should use parameters rather than concatenated strings...