Linq-to-SQL 日期格式：查询不起作用

Question

我有一个存储过程，当我在 SQL Server 中执行此查询时，

exec SelectOfficeNameGroup  '2011-10-05', '2011-11-09', ''

它返回 110 行。

现在我的 c# 3.5 表单上有两个日期选择器和一个执行查询的按钮，如下所示：

var result = context.SelectOfficeNameGroup(dateTimePickerFrom.Value, dateTimePickerTo.Value, "");

但它只返回 2 行。

我的日期选择器的格式是 MM/DD/YYYY

这是我的程序

ALTER PROC [dbo].[SelectOfficeNameGroup]
  @From datetime,
  @To datetime,
  @OfficeName nvarchar(50)
AS
BEGIN
   SELECT
      ID AS ProductID, OfficeName,  
      SUM(Quantity) AS Quantity, 
      SUM(TotalPrice) AS TotalPrice, 
      Category
   FROM
      ProductLogWithCategory 
   WHERE 
      DateTime BETWEEN @From AND @To AND OfficeName = @OfficeName
   GROUP BY 
      OfficeName, ID, Category

  return
END

有什么建议吗？

Answer 1

尝试

var result = context.SelectOfficeNameGroup(dateTimePickerFrom.Value.ToString("yyyy-MM-dd"), dateTimePickerTo.Value.ToString("yyyy-MM-dd"), "");

查看DateTime.ToString ^文档< /a>

---

更新

为了安全起见，尝试

ALTER PROC [dbo].[SelectOfficeNameGroup]
  @From varchar(8),
  @To varchar(8),
  @OfficeName nvarchar(50)
AS
BEGIN
   SELECT
      ID AS ProductID, OfficeName,  
      SUM(Quantity) AS Quantity, 
      SUM(TotalPrice) AS TotalPrice, 
      Category
   FROM
      ProductLogWithCategory 
   WHERE 
      DateTime BETWEEN @From AND @To AND OfficeName = @OfficeName
   GROUP BY 
      OfficeName, ID, Category

  return
END

像这样使用它

来自 sql

exec SelectOfficeNameGroup  '20111005', '20111109', ''

来自 linq

var result = context.SelectOfficeNameGroup(dateTimePickerFrom.Value.ToString("yyyyMMdd"), dateTimePickerTo.Value.ToString("yyyyMMdd"), "");

Answer 2

DateTime.Parse('your date in MM/DD/YYYY', new CultureInfo("en-US")).ToString("yyyy-MM-dd")