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)