[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Crystal Reports - Dealing With Daylight Saving

Posted on 2008-11-08
8
Medium Priority
?
2,235 Views
Last Modified: 2013-12-17
Hi,

I have a database which stores calls logged by our support desk.  All of the dates in the DB are stored in UTC.  I am writing reports in Crystal XI which produce some stats around call volumes etc and so are very date/time dependent.  The problem is that because the dates are stored in the DB in UTC format I lose the actual time that the call was logged when daylight saving is operating.  For example between the end of march and october we add an hour onto our day (BST - british summer time), however the database stores in UTC so when pulling them into Crystal they appear an hour earlier than they were actually logged.  (I really hope this makes sense!).

My question is, do any of you know if there is a tried and tested method/formula i can use to get Crystal to display the dates/times correctly.

If it helps, one of our SQL guys gave me a piece of code which does this based on a fixed variable - i've tried to convert it to a Crystal formula but failed miserably.

Thanks Guys n Gals.

Dave
declare @logdate datetime
 
set @logdate = '25 feb 2006 10:30'
 
select 
 
case 
 
when @logdate between dateadd(dd, -datepart(dw, '31 mar '+cast(year(@logdate) as varchar))+1, '31 mar '+cast(year(@logdate) as varchar)) and dateadd(dd, -datepart(dw, '31 oct '+cast(year(@logdate) as varchar))+1, '31 oct '+cast(year(@logdate) as varchar)) then dateadd(hh, 1, @logdate)
 
else @logdate
 
end

Open in new window

0
Comment
Question by:dcsearle
  • 3
  • 2
6 Comments
 
LVL 35

Expert Comment

by:James0628
ID: 22912680
If the time is changed on specific dates, it may be fairly easy to have a formula adjust the time if the date is between those dates.  The down side is that if those dates change (eg. BST starts earlier one year), the dates in the formula will have to be manually changed.

 OTOH, if the change happens on something a bit more general, like the third Sunday in the month, things get more complicated.

 Also, what time of day does the change take place?  You'd have to factor that into the formula.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22914517
Agree.  Is BST changed on the same days and time as US Daylight Svaings Time?

This year it ended on 2 Nov.  I don't recall when it started but the end of March sounds about right.

Wht years of data are you concerned about?

mlmcc
0
 
LVL 1

Author Comment

by:dcsearle
ID: 22924888
hi,

thanks for the reponses guys.

Sorry, should have been clearer, BST is defined as

"the period beginning at one o'clock, Greenwich mean time, in the morning of the last Sunday in March and ending at one o'clock, Greenwich mean time, in the morning of the last Sunday in October."
wikipedia

so a formula along those lines would be great

cheers,

dave
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 22927554
OK, try the following.  It should return the original datetime, or, if the original datetime falls between 1 AM on the last Sunday of March and 1 AM on the last Sunday of October, the original datetime + 1 hour.  I've tested it on some semi-random datetimes and it seems to be working, but some more testing, especially with key dates and times (eg. 03/31 and 10/31 at 12 and 1 AM) might not be a bad idea.  Replace {table.datetimefield} with your field name.  For testing purposes, I'd create a formula with this in it and put it on the report next to your datetime field, so that you can compare them.

Local DateTimeVar BST_start;
Local DateTimeVar BST_end;

BST_start := DateTime (Year ({table.datetimefield}), 03, 31, 1, 00, 00);

while DayOfWeek (BST_start) <> 1
do
  BST_start := DateAdd ("d", -1, BST_start);

BST_end := DateTime (Year ({table.datetimefield}), 10, 31, 1, 00, 00);

while DayOfWeek (BST_end) <> 1
do
  BST_end := DateAdd ("d", -1, BST_end);

if {table.datetimefield} in BST_start to BST_end then
  DateAdd ("h", 1, {table.datetimefield})
else
  {table.datetimefield}



 There are some tweaks you might want to consider, depending on your circumstances.

 BST_start and BST_end will be different for each year, so they're based on the year from your datetime field.  If all of the dates in a given report are going to be in the same year (say the report is always run for a range of dates in the same calendar year), you could have a formula in the report or page header that sets BST_start and BST_end.  That would theoretically be more efficient than setting them with every record.  I wouldn't actually expect it to have a significant/noticeable impact on the report's performance, but if you have a lot of records ...    Who knows?  If you decide to do that, BST_start and BST_end would need to be global or shared variables, so the values were available in different formulas.

 If you're going to be using the formula in multiple places (either on multiple datetime fields in a single report, or different fields in different reports), you could generalize it a bit by declaring a local datetime variable, assigning the datetime field to that variable, and replacing all of the references to the datetime field in the current formula with that variable name.  That way if you wanted to use the formula with a different field, you'd only have to change one reference, when the field was assigned to that variable.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22934026
Looks like a good formula.

Not sure what I would have come up with.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 22935857
Neither was I, until I started working on it.  :-)  I had this vague idea about looking at which day of the week the date was and trying to figure out if it was the last Sunday in the month, or something like that, then realized that I could easily find the last Sunday of each month by just starting on the last day of the month and working my way backwards.  I suppose there could be a more efficient way to handle it (without the loops), but I doubt there's one that's much easier to understand.

 James
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
Have you ever run into that annoying problem where the computer won't boot?  Wouldn't it be great if you had a tool that would make that disk boot again?  I have found one tool that works more often than not ...
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question