User Forum

How can I set up a report to run from 8:00am yesterday to 7:59am today without having to specify the date each time?

 Thank you for the forum question - this one is a bit challanging to do out of the box. But there is a custom solution that can be implemented using the following process:

1. Create a SQL Function called zTimeSpan on the MDB Database (SQL Code Below)
2. Add CustomAHD.TXT file entries to utilize the custom time span (Sample Below)
3. Create Reports in Report Builder - Criteria can be added to show dates in or out of the specified custom time span

Using SQL Server Management Studio run the query below to create the scalar value function named zTimeSpan. Essential the function takes three date arguments - Comparative Date, Start Date, and End Date. The function will return a "1" if the Comparative date is greater than or equal Start Date and less then End Date. The function will return a "0" if the Comparative Date is not within the Start and End dates.

SQL Function Code:

USE [mdb]
GO
/****** Object:  UserDefinedFunction [dbo].[zTimeSpan]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[zTimeSpan](@cDate datetime, @sDate datetime, @eDate datetime)
RETURNS int
AS
BEGIN
DECLARE @ans int
IF (@cDate >= @sDate and (@cDate < @eDate))
BEGIN
 SET @ans = 1
END
ELSE
BEGIN
 SET @ans = 0
END
RETURN @ans
END
 

Add the following entries to CUSTOMAHD.TXT to use the function. An entry will have to added for each custom time span. The time spans below are from 8:00 am yesterday and 8:00 am today. The field with the (EDT) specification has to be used while Daylight Savings time is in effect. For users in other timezones use the appropriate offsets.

[Field]
INC
Custom Time Span (EDT)
dbo.zTimeSpan(dateadd(ss, INC.open_date - (4*3600), convert(datetime, '1/1/1970 0:00')), CONVERT(datetime, CONVERT(char(10), dateadd(dd, -1, getdate()), 101) + ' 8:00 am'), CONVERT(datetime, CONVERT(char(10), getdate(), 101) + ' 8:00 am'))
num
INC
Custom Fields

[Field]
INC
Custom Time Span (EST)
dbo.zTimeSpan(dateadd(ss, INC.open_date - (5*3600), convert(datetime, '1/1/1970 0:00')), CONVERT(datetime, CONVERT(char(10), dateadd(dd, -1, getdate()), 101) + ' 8:00 am'), CONVERT(datetime, CONVERT(char(10), getdate(), 101) + ' 8:00 am'))
num
INC
Custom Fields

Create your report in the Report Builder client - When adding these fields, use the criteria builder to specify a 0 or 1. (1 = True - Date is in Time Span)

Please post questions on this implementation to the forum!

Thanks,