Crystal Reports - Dealing With Daylight Saving


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.

declare @logdate datetime
set @logdate = '25 feb 2006 10:30'
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

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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?

dcsearleAuthor Commented:

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."

so a formula along those lines would be great


Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
  BST_start := DateAdd ("d", -1, BST_start);

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

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

if {table.datetimefield} in BST_start to BST_end then
  DateAdd ("h", 1, {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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Looks like a good formula.

Not sure what I would have come up with.

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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.