我需要在 where 语句中动态设置日期，以便仅从表中提取结果以在县的财政年度中发生

Question

这是我当前的代码：

SELECT     nature, inci_id, calltime, agency, gp
FROM         inmain AS inmain
WHERE     (calltime >= CONVERT(DATETIME, '2013-07-01 00:00:00', 102))
      AND (calltime <= CONVERT(DATETIME, '2014-06-30 23:59:59', 102))
ORDER BY nature, calltime

我需要它在每个财政年度结束时提取数据，而无需一直输入并更改日期，但是我不知道如何使其更改，使其介于7/1/（上一年）到6/30/（当年）。

我们使用自动化程序在财政年度结束时将其发送出去。这样我就不必一直手动完成。

Answer 1

使用日期部分年份，这样您就不会对函数执行搜索 我建议在主查询运行之前设置一些会困扰的变量，这样您的 sarg 就针对日期。

declare @fiscalStart = select cast('07/01/' + 
        Cast((datepart(year, getdate())-1) as varchar(4)) as datetime)
declare @fiscalEnd = select dateadd(second, -1, cast('07/01/' + 
        Cast(datepart(year, getdate()) as varchar(4)) as datetime))

SELECT     nature, inci_id, calltime, agency, gp
FROM         inmain AS inmain
WHERE calltime between @fiscalStart and @fiscalEnd
ORDER BY nature, calltime

Answer 2

我想你想做的是这样的：

SELECT     nature, inci_id, calltime, agency, gp
FROM         inmain AS inmain
WHERE     (calltime >= CONVERT(DATETIME, CAST(YEAR(GETDATE()) - 1 AS VARCHAR(10)) + '-07-01 00:00:00', 102))
      AND (calltime <= CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR(10)) + '-06-30 23:59:59', 102))
ORDER BY nature, calltime

但您也可以使用变量使代码更具可读性，如下所示：

DECLARE @CurrentYear INT = 2015
DECLARE @CurrentYearAsString VARCHAR(10) = CAST(@CurrentYear AS VARCHAR(10))
DECLARE @PreviousYearAsString VARCHAR(10) = CAST(@CurrentYear - 1 AS VARCHAR(10))
SELECT     nature, inci_id, calltime, agency, gp
FROM         inmain AS inmain
WHERE     (calltime >= CONVERT(DATETIME, @PreviousYearAsString + '-07-01 00:00:00', 102))
      AND (calltime <= CONVERT(DATETIME, @CurrentYearAsString + '-06-30 23:59:59', 102))
ORDER BY nature, calltime