Virtual Calendar defining different start end times

John-S Pretorius
John-S Pretorius used Ask the Experts™
I need to create a Virtual calendar that defines starting and ending hours different to a standard day. A standard day, today, for example, December 20, 2019, started from 00:00:00 to 23:59:59. The new virtual 12-20-2019 should start from 12-19-2019 03:00:00 to 12-20-2019 02:59:59.

The month of November should include 11-01-2019 03:00:00 to 12-01-01 02:59:59 and each day should be defined as above with the exception of the 1st which always starts from 03:00:00 and the 30th includes the 3 hours of December 1st.

The year (2019) should include 01-01-2019 03:00:00 to 01-01-2019 02:59:59 with the months and days defined as mentioned above.

Even though I'm using Crystal reports it probably would be easier creating the mentioned in SQL.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Just add 3 hours to the actual datetime to determine the virtual "date", then use the actual time, unadjusted, as the time.
John-S PretoriusSystems Engineer


LOL, I was totally over thinking this. Thank you Scott, certainly going to remember this.

Your approach proved solid in Crystal reports by creating a new time formula. This brought the 2 dates (12/20 and 12/21) when grouping by day.

if {REVNONREVTRANSACTION.Time} <= DateAdd("h",3,CurrentDate) then DateAdd("h", -3, {REVNONREVTRANSACTION.Time}) else {REVNONREVTRANSACTION.Time}

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial