Select a Variable TimeSpan with an Arbitrary End point

Question

I've been working on a Stored Procedure that checks the time, then retrieves records going back over the last full 24 hour period between 8am and the previous 8am. So, for instance, assume that it's currently 10am. The stored procedure looks at the current time, notes that it is past 8am, and sets the query to run backwards 24 hours, from 8am today to 8a yesterday. If it were, say, 7am, the query would be set to check from 8am yesterday to 8am the day before. This was actually relatively simple to do. The SP is meant to be used to retrieve records for a report tracking jobs completed in the given time span.

However, they've come back at me and asked me to change the stored procedure such that the hour the report ends at, and the span of time checked, is configurable from the front-end of the site. I have this working for TimeSpans greater or equal to 24 hours, but am having trouble with spans under that. Here's what I have so far for my logic in the Stored Procedure -

-- Retrieves data on jobs that completed/completed with errors during a given time span.
DECLARE @Hour NVARCHAR(2)
DECLARE @TimeFrame NVARCHAR(2)
DECLARE @TimeSpan INTEGER

SET @TimeSpan = 24
SET @TimeFrame = 'AM'
SET @Hour = '08'

DECLARE @dateStart DateTime
DECLARE @dateEnd DateTime

IF @TimeSpan < 24 -- Our TimeSpan is under one full day.
    BEGIN
      IF GETDATE() > DATEADD(hh, 0, DATEDIFF(hh, 0, GETDATE())) + (@Hour + ':00:00 ' + @TimeFrame)
      BEGIN
       SET @dateStart = DATEADD(hh, 0, DATEDIFF(hh, 0, GETDATE() - (1 + @TimeSpan))) + (@Hour + ':00:00 ' + @TimeFrame)
       SET @dateEnd =  DATEADD(hh, 0, DATEDIFF(hh, 0, GETDATE())) + (@Hour + ':00:00 ' + @TimeFrame)
      END
     ELSE
      BEGIN
       SET @dateStart = DATEADD(hh, 0, DATEDIFF(hh, 0, GETDATE() - (2 + @TimeSpan))) + (@Hour + ':00:00 ' + @TimeFrame)
       SET @dateEnd =  DATEADD(hh, 0, DATEDIFF(hh, 0, GETDATE() - 1)) + (@Hour + ':00:00 ' + @TimeFrame)
      END
    END
ELSE -- Our TimeSpan is at least one full day.
    BEGIN
     IF GETDATE() > DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) + (@Hour + ':00:00 ' + @TimeFrame)
      BEGIN
       SET @dateStart = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE() - (1 + (@TimeSpan/24)))) + (@Hour + ':00:00 ' + @TimeFrame)
       SET @dateEnd =  DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) + (@Hour + ':00:00 ' + @TimeFrame)
      END
     ELSE
      BEGIN
       SET @dateStart = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE() - (2 + (@TimeSpan/24)))) + (@Hour + ':00:00 ' + @TimeFrame)
       SET @dateEnd =  DATEADD(D, 0, DATEDIFF(D, 0, GETDATE() - 1)) + (@Hour + ':00:00 ' + @TimeFrame)
      END
    END

@Hour, @TimeFrame and @TimeSpan are all parameters ready to be set by the report front-end, and default to '08', 'AM' and 24 respectively. I know that setting the day range works properly in the ELSE portion of the top-level if. I'm reasonably certain that the issue with setting the hour offset lies in how I'm using the DATEADD and DATEDIFF function here, but I haven't been able to figure out just where I'm making my miss step. Any help here would be greatly appreciated.

Answer 1

Why not just use a query like:

SELECT something
FROM aTable
WHERE timeCompleted BETWEEN @StartDate AND @EndDate

If you are concerned about the time being correct (i.e. the dates must be '01/01/1900 13:00:00' or something like that) just pass in the date by itself and add the time as a function in the SQL.

If this is not what you are intending please include more of the SQL so that we have a better understanding of what you are trying to accomplish.

Answer 2

-- convert the required end hour to 24 hour format
DECLARE @EndHour INT
SET @EndHour = CASE WHEN @TimeFrame = 'AM' THEN @Hour ELSE @Hour + 12 END

-- set @DateEnd to the date-only portion of the current datetime
-- ie, the time portion will be set to 00:00:00
SET @DateEnd = DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))

-- add the required end hour
SET @DateEnd = DATEADD(hour, @EndHour, @DateEnd)

-- if @DateEnd is in the future then subtract 24 hours from it
IF @DateEnd > GETDATE()
   SET @DateEnd = DATEADD(hour, -24, @DateEnd)

-- set @DateStart by subtracting the required timespan from @DateEnd
SET @DateStart = DATEADD(hour, -@TimeSpan, @DateEnd)