SQL数据错误：一个VARCHAR数据类型到datetime数据类型的转换导致超出范围的值 [英] SQL data error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

问题描述

我有一个的返回该错误的SQL查询：

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

一个varchar数据类型为datetime数据类型的转换导致的超出范围值

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

下面的查询：

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.

推荐答案

您的问题是文化相关的...你永远不应该依赖于特定文化的日期时间格式！否则，你不得不告诉T-SQL 转换函数的特定格式（这应该是你的情况101）...

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

刚刚尝试这一点：

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

最好是使用ISO8601 2016-01-22T18：59 ：00

或的ODBC格式，它是有一个

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

这篇关于SQL数据错误：一个VARCHAR数据类型到datetime数据类型的转换导致超出范围的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！