Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
848 views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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...


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...