adjusting zulu time for daylight savings time in crystal reports

Good Morning!

I have a database which collects time in Zulu time and I have been using the DateAdd function and subtracting 7 or 8 hours depending on the time of year for daylight savings time.  When working within a single year I have been using the following to adjust for DST:

Local DateTimeVar DST_start;
Local DateTimeVar DST_end;

DST_start := DateTime (Year ({E05.E05_03}), 03, 14);

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

DST_end := DateTime (Year ({E05.E05_03}), 11, 7);

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

if {E05.E05_03} in DST_start to DST_end then
  Date(DateAdd ("h", -7, {E05.E05_03}))
else
  Date(DateAdd("h", -8, {E05.E05_03}))

What I need now is a formula that will allow me to cover all years since the days DST changes each year.  However, I have found it changes on the second Sunday in March (spring forward) and then reverts back on the first Sunday in November.  Is there anything out there like this?  I have searched EE and just found old posts on the topic.

Thanks in advance,

Task
taskhillAsked:
Who is Participating?
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.

mlmccCommented:
How far back do your dates go?
I ask because the start and end dates changed a few years back.  If you are only concerned with recent dates and the future the below should work.

Ken Hamady's page has a bunch of formulas
http://kenhamady.com/formulas/default.html

Adjusting the one for the first Friday of the month to get the 2nd Sunday in March

  WhileReadingRecords;
  DateVar D:= Date(2015,3,1);  //Today's Date or any date field
  Numbervar DOW:= 1; //The day of week you want with 1 being Sunday, 2 = Monday, etc
  NumberVar Week:= 2; // The Week you want, 1 being the FIRST Monday of the month, 2 being the second, etc
  DateVar BOM:= D - Day(D)+(8-DOW);
  DateVar BOW:= BOM - DayOfWeek(BOM) + DOW  + (7*(Week-1))

Open in new window


You could replace the YEAR in the first line to use your datefield
  WhileReadingRecords;
  DateVar D:= Date(Year({YourDateField}),3,1);  //Today's Date or any date field
  Numbervar DOW:= 1; //The day of week you want with 1 being Sunday, 2 = Monday, etc
  NumberVar Week:= 2; // The Week you want, 1 being the FIRST Monday of the month, 2 being the second, etc
  DateVar BOM:= D - Day(D)+(8-DOW);
  DateVar BOW:= BOM - DayOfWeek(BOM) + DOW  + (7*(Week-1))

Open in new window


Similarly for the 1st Sunday in November

  WhileReadingRecords;
  DateVar D:= Date(Year({YourDateField}),11,1);  //Today's Date or any date field
  Numbervar DOW:= 1; //The day of week you want with 1 being Sunday, 2 = Monday, etc
  NumberVar Week:= 1; // The Week you want, 1 being the FIRST Monday of the month, 2 being the second, etc
  DateVar BOM:= D - Day(D)+(8-DOW);
  DateVar BOW:= BOM - DayOfWeek(BOM) + DOW  + (7*(Week-1))

Open in new window


You could then use those to compare your date to and add the appropriate offset

mlmcc
0
taskhillAuthor Commented:
I need to go back to 2011 and forward.
0
mlmccCommented:
According to Wikpedia the current dates have been in effect since 2007.

Does it matter for locale since some states don't go to DST (Arizona,  Hawaii)

mlmcc
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

taskhillAuthor Commented:
Yes it changed in 2007 and I have been only running reports for each year so I would change the day in lines "DST_start" and "DST_end" accordingly.  Now I need to run a report that spans across 2011-2015 which has different days in both March and November.  I need a formula that does this so I can run aggregate data reports for all years.

The formulas you listed from the site may be useful I will work on them tomorrow when I return to work.

Thank you for your help so far.
0
James0628Commented:
What's wrong with the formula that you posted?  It seems like it should work.  It starts on 03/14 or 11/07 for the year in E05_03, and counts backwards from each one until it gets to Sunday.  As long as your dates don't go back to when the DST period was different, I don't see a problem.

 Having said that, I just did some playing around and came up with a couple of alternatives that seem to work.  I haven't tested them thoroughly or anything, but they seemed to produce the correct result for recent years.

DST_start := Date (Year ({E05.E05_03}), 3, 1) + ((8 - DayOfWeek (Date (Year ({E05.E05_03}), 3, 1))) mod 7) + 7

DST_end := Date (Year ({E05.E05_03}), 11, 1) + ((8 - DayOfWeek (Date (Year ({E05.E05_03}), 11, 1))) mod 7)

 James
0

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
taskhillAuthor Commented:
James - I had no idea the formula counted backwards (I have very little Crystal experience).  After researching this morning, I now see it will be okay as you mentioned.

I will also play around with the formula you and mlmcc posted to gain more experience.


Thanks again.
0
James0628Commented:
You're welcome.

 James
0
James0628Commented:
There was something else that had crossed my mind, but I forgot to mention it.

 Your formula doesn't check the time.  DST_start and DST_end will just have the default time of midnight, so they'll both be 2 hours early.

 If you won't have any records for those 2 hours (eg. these records are for some kind of transactions that only happen during the work week and not on the weekend), then that isn't a problem.  But if you could have records with times between midnight and 2 AM on Sunday, then DST_start and DST_end should presumably include a time, which I guess would be 2 AM.

 FWIW, the same thing goes for the statements that I posted.  They only produce dates, without a time.

 James
0
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
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.