当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Query not returning the correct records

问题描述:

Hello I have this code I am trying to fix, the issue is that when I pass a parameter within the same year say

startDate = 01/01/2011 EndDate 07/01/2011

It works it returns the correct data but the issue is when I try to use it with a different year, say

startDate = 02/01/2011 EndDate 01/25/2012

--(wont work shows blank all thought there is data in both years).

Here is my sql code

ALTER PROCEDURE [dbo].[GetOrderByDateRange]

@startDate nvarchar(50),

@endDate nvarchar(50)

AS

BEGIN

DECLARE @days varchar(100)

set @days = DATEDIFF(d, @StartDate, DATEADD(day,+1,@EndDate))

SELECT distinct(CONVERT(char(10), OrdDate, 101)) as OrdDate,

COUNT(PurchaseId) as OrdCount,

SUM(Total) as OrdTotals,

AVG(Total) as AvgOrdAmount,

SUM(SubTotal) as Net,

@days as 'Days'

FROM [PurchaseOrders]

WHERE CONVERT(char(10), OrdDate, 101) >= @startDate

AND CONVERT(char(10), OrdDate, 101) <= @endDate

GROUP BY CONVERT(char(10), OrdDate, 101)

END

GO

网友答案:

Don't use CONVERT... assuming OrdDate is a DATETIME column, it should be:

CREATE PROCEDURE dbo.GetOrderByDateRange
    @StartDate DATE, -- pass 'yyyy-mm-dd' format
    @EndDate   DATE
AS
BEGIN
    SET NOCOUNT ON;
...
    WHERE OrdDate >= @startDate AND OrdDate < DATEADD(DAY, 1, @endDate)
END
GO
网友答案:

Try to convert the format of your date to the standard which is yyyy-MM-dd

Hope this is helpful

分享给朋友:
您可能感兴趣的文章:
随机阅读: