sql日期格式:查询不工作

本文关键字：工作 查询 日期 格式 sql | 更新日期: 2023-09-27 18:15:43

当我在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

任何建议吗?

sql日期格式:查询不工作

Try with 

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

看一下日期时间。ToString <一口>文档

为了安全起见，请尝试

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

并像这样使用

from sql

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

从linq 

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

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