Crystal Reports - Dealing With Daylight Saving

Posted on 2008-11-08
Last Modified: 2013-12-17

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

Question by:dcsearle
    LVL 34

    Expert Comment

    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.

    LVL 100

    Expert Comment

    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?

    LVL 1

    Author Comment


    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


    LVL 34

    Accepted Solution

    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.

    LVL 100

    Expert Comment

    Looks like a good formula.

    Not sure what I would have come up with.

    LVL 34

    Expert Comment

    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.


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
    Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
    The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.
    The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now