从字符串转换日期和/或时间时转换失败

本文关键字：转换 时间 失败 字符串 日期 | 更新日期: 2023-09-27 18:20:41

此查询在Sql Server Management Studio中运行良好，但当我尝试在数据集&从datetime选取器传递Date（@Param2&@Pram3），其引发异常Conversion失败。我不知道这是怎么回事，我的其他查询都很好。这是因为UNION ALL吗？

.NET代码

startDatePicker.Format = DateTimePickerFormat.Short;
endDatePicker.Format = DateTimePickerFormat.Short;
DataTable ntwTable = ntw.GetData(owner,start,end);
start = startDatePicker.Value.ToShortDateString();
end = endDatePicker.Value.ToShortDateString();

SQL

SELECT        o.Name, SUM(sq.SQuantity * sq.SRate) AS TotalSale, SUM(sq.PrQuantity * sq.PrRate) AS TotalPurchase, (SUM(sq.PQuantity) - SUM(sq.SQuantity)) 
                                 * (SUM(sq.PQuantity * sq.PRate) / SUM(sq.PQuantity)) AS Inventory
        FROM            (SELECT        OwnerId, CompanyId, Quantity AS PQuantity, RatePerShare AS PRate, 0 AS SQuantity, 0 AS SRate, 0 AS PrQuantity, 0 AS PrRate, Date
                                  FROM            Purchase
                                  UNION ALL
                                  SELECT        OwnerId, CompanyId, 0 AS PQuantity, 0 AS PRate, 0 AS SQuantity, 0 AS SRate, Quantity AS PrQuantity, RatePerShare AS PrRate, Date
                                  FROM            Purchase AS pr
                                  UNION ALL
                                  SELECT        OwnerId, CompanyId, Quantity AS PQuantity, 0 AS PRate, 0 AS SQuantity, 0 AS SRate, 0 AS PrQuantity, 0 AS PrRate, Date
                                  FROM            Bonus
                                  UNION ALL
                                  SELECT        OwnerId, CompanyId, Quantity AS PQuantity, CostOfShare AS PRate, 0 AS SQuantity, 0 AS SRate, 0 AS PrQuantity, 0 AS PrRate, Date
                                  FROM            RightShare
                                  UNION ALL
                                  SELECT        OwnerId, CompanyId, 0 AS PQuantity, 0 AS PRate, Quantity AS SQuantity, RatePerShare AS SRate, 0 AS PrQuantity, 0 AS PrRate, Date
                                  FROM            Sales) AS sq INNER JOIN
                                 Owner AS o ON o.OwnerId = sq.OwnerId
        WHERE        (o.OwnerId = @Param1) AND (sq.Date BETWEEN @Param2 AND @Param3)
        GROUP BY sq.CompanyId, o.Name

从字符串转换日期和/或时间时转换失败

在您的代码中，失败有两种（明显的）可能性。

第一个可能是date列中的隐式转换错误。如果purchase.date是日期/时间值，但union all中的一列不是：SSMS中的bonus.date，rightshares.date , or sales.date. To be honest, I would be surprised if this is the case. However, it is easy enough to check for. Just run the union all`查询以查看是否发生错误，则会发生这种情况。

第二种可能性是@Param1或@Param2有问题。这些似乎是作为字符串传递的。您可以通过查看字符串并执行以下操作来测试这一点：

select cast(@Param1 as datetime), cast(@Parame2 as datetime)

一个可能的问题是操作系统和数据库级别的日期格式不同。例如，在一种情况下，2013年1月20日可能是1月20号，而在另一种情况中则是一个错误。如果出现此问题，可以使用convert()将参数转换为正确的格式（在数据库中），或者在传递参数之前进行转换。

我确信这不是最好的答案，但这似乎是由于区域设置。如果您更改以下两行：

start = startDatePicker.Value.ToShortDateString();
end = endDatePicker.Value.ToShortDateString();

收件人：

start = startDatePicker.Value.ToString("yyyy-MM-dd HH:mm:ss")
end = endDatePicker.Value.ToString("yyyy-MM-dd HH:mm:ss")

那么我相信这应该很好。

（CONVERT（CHAR（10），sq.Date，111）BETWEEN@Param2 AND@Param3）

只需要转换。

相关文章：