Page 1 of 1
Report Date "Business Start Time" setting?
Posted: Thu Apr 10, 2008 4:42 pm
by 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?
Re: Report Date "Business Start Time" setting?
Posted: Fri Apr 11, 2008 1:54 am
by 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.
Re: Report Date "Business Start Time" setting?
Posted: Fri Apr 11, 2008 11:39 am
by 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
Re: Report Date "Business Start Time" setting?
Posted: Tue May 20, 2008 5:05 am
by 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?
Re: Report Date "Business Start Time" setting?
Posted: Fri May 30, 2008 5:30 am
by 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.
Re: Report Date "Business Start Time" setting?
Posted: Fri May 30, 2008 10:37 am
by 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