I need to dynamically set the date in a where statement to only pull results from a table to occur in County's fiscal year

Question

Here is my current code:

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

I need this to pull data at the end of every fiscal year without going in and changing the date all the time, but I am at a loss as how to make it just change so it is between 7/1/(of the previous year) to 6/30/(of the current year).

We use an automation program to send this out at the end of the fiscal year. That way I don't have to do it manually all the time.

Answer 1

Use datepart year, so your not performing a search on a function I'd reccomend setting up some variables that wil beset prior to the main query running, so your sarg is against a date.

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

I think what you want to do is this:

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

But you could also use variables to make the code more readable as follows:

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