如何运行基于结束日期的年到日期查询

Question

我有一个基于选择的"结束日期"运行的查询。本质上，用户选择结束日期，查询将基于结束日期运行整个YTD的报告。例如，如果我选择"12/3/12"，它应该运行从1/1/12到12/3/12的报告。这是有效的，如果你运行它的一天…今天我试着运行它的结束日期"12/31/12"，但我没有返回结果，因为我认为它试图获得基于今天的日期开始日期?下面是我的查询:

SELECT        Store_Number, COUNT(DISTINCT Customer_Email_Address) AS Customer_email_address, COUNT(DISTINCT Invoice_Number) AS [Total Cars], 
                         @enddate AS End_Date, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS Start_Date
FROM            Invoice_Tb
WHERE        (Invoice_Date BETWEEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AND CONVERT(Datetime, @enddate, 102))
GROUP BY Store_Number

Answer 1

将GetDate()替换为@enddate参数，应该可以工作:

SELECT        
    Store_Number, 
    COUNT(DISTINCT Customer_Email_Address) AS Customer_email_address, 
    COUNT(DISTINCT Invoice_Number) AS [Total Cars], 
    @enddate AS End_Date, 
    DATEADD(yy, DATEDIFF(yy, 0, @enddate), 0) AS Start_Date
FROM Invoice_Tb
WHERE (Invoice_Date BETWEEN DATEADD(yy, DATEDIFF(yy, 0, @enddate), 0) 
                    AND CONVERT(Datetime, @enddate, 102))
GROUP BY Store_Number

如果您查询:

declare @enddate datetime = '12/31/2012'
select DATEADD(yy, DATEDIFF(yy, 0, @enddate), 0)

它将返回你想要的2012-01-01