Report Date "Business Start Time" setting?

[nickc]

Quick inspection of /Admin/Reports/DailySales.aspx shows reporting date initialized to "LocalNow" and manipulated by DateTime.AddDays(), etc. Does the CommerceBuilder report method (ReportDataSource.GetDailySales(DateTime)) have a database or other setting anywhere to control the boundary time for date, which appears to be arbitrarily set around 6am?

[m_plugables]

Well after inspecting the report i do not think so that there is a way to control the boundary time using this ReportDataSource.GetDailySales(DateTime) function.

[nickc]

To reconcile your reports with Able reports, you can use this date manipulation:

Code: Select all

/****** Object:  UserDefinedFunction [dbo].[fnGetBusinessDate]    Script Date: 04/11/2008 09:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Nick Cole
-- Create date:4/10/2008
-- Description:Generate Business Date based on passed hour, minute args
-- Defaults to 6:00am boundary time, alternate boundary time can be passed in
-- e.g. "fnGetBusinessDate(Table.DateColumn) between @StartDate and @EndDate" -- 6am boundary time
--       "fnGetBusinessDate(Table.DateColumn, 0, 0) between @StartDate and @EndDate" -- midnight boundary time
-- =============================================
CREATE FUNCTION [dbo].[fnGetBusinessDate] 
(
	@ActualDate datetime,
	@StartHour int = 6,
	@StartMinute int = 0
)
RETURNS datetime
AS
BEGIN
	DECLARE @BusinessDate datetime
	DECLARE @ActualHour int
	DECLARE @ActualMinute int

	SELECT @ActualHour = datepart(hh, @ActualDate)
	SELECT @ActualMinute = datepart(mi, @ActualDate)

	IF ((@ActualHour*60+@ActualMinute) >= (@StartHour*60+@StartMinute))
	BEGIN
		SELECT @BusinessDate = convert(char,@ActualDate,101)
	END
	ELSE
	BEGIN
		SELECT @BusinessDate = convert(char,@ActualDate - 1,101)
	END

	RETURN @BusinessDate

END

[sohaib]

.....have a database or other setting anywhere to control the boundary time for date, which appears to be arbitrarily set around 6am?

The boundary time in code is correctly set to midnight. This could possibly be a timezone issue. What timezone offset do you have set in your store settings?

[sohaib]

We created a bug in our bug database based on this form post but we couldn't reproduce the bug. We haven't received any further information so we will be closing the bug as invalid.

If you have anything else to add please do let us know.

[nickc]

Yes, not worth pursuing. Able version I'm using is too old.

Issue is actually that ac_Orders datetime is UTC, ac_PageViews datetime is UTC + TZ offset.
When I bound the two tables together in a view, couldn't get the numbers to jive as page view data was always 7 hours ahead. I've hacked around it.

-Nick