Link to home
Start Free TrialLog in
Avatar of 567082836
567082836Flag for United States of America

asked on

Crystal Reports Time Issues - Time Zone

I am using the CurrentDate to find out when a report is run.  

Since the reports are being run on a Japan Server, it is 16 hours time difference.  What function can I use, all I want to see on the report is as follows:

Example:

         11:23 am  

This time is West Coast/Los Angeles Time.  

16 hours time difference between japan and Los Angeles, I don't think we can do a date subtraction unless it can account for daylight savings.
Avatar of Mike McCracken
Mike McCracken

You could account for it with a formula.

DateAdd('h',-16,{YourDateField})

If you only want the time then

Time(DateAdd('h',-16,{YourDateField}))

To account for daylight savings would need a test

For 2015
If {YourDateField} < DateTime(2015,3,8,2,0,0) OR {YourDateField} > DateTime(2015,11,1,2,0,0) then
    DateAdd('h',-15,{YourDateField})
Else
    DateAdd('h',-16,{YourDateField})


That formula can be changed using the technique in this question to account for the changing years
https://www.experts-exchange.com/questions/28696031/adjusting-zulu-time-for-daylight-savings-time-in-crystal-reports.html

mlmcc
mlmcc
Avatar of 567082836

ASKER

Ok I like this!

But how can I handle daylight savings for the years ahead.  I will run this report in a scheduler for years to come... possible?  

2016, 2017, 2018..etc..

Thanks!
2 Ways to handle future DST.

1 - Easy way.  On 1 Jan (or thereabouts) review the report and update the date that DST changes.
This is probably something that needs to be done anyways to ensure the report is still useful and needed.

2.  Follow the method outlined in the linked question to calculate the 2nd Sunday in March and the 1sr Sunday in November.
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

Name Find_DST_Start
  WhilePrintingRecords;
  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 DST_Start_BOW:= BOM - DayOfWeek(BOM) + DOW  + (7*(Week-1))

Open in new window


Name- Find_DST_End
  EvaluateAfter({@Find_DST_Start});
  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 DST_End_BOW:= BOM - DayOfWeek(BOM) + DOW  + (7*(Week-1))

Open in new window


Name- Disp_Adjusted_time
EvaluateAfter({@Find_DST_End});
DateVar DST_Start_BOW;
DateVar DST_End_BOW;

If {YourDateField} < DST_Start_BOW OR DST_End_BOW > then
     DateAdd('h',-15,{YourDateField})
 Else
     DateAdd('h',-16,{YourDateField})

Open in new window


mlmcc
The formulas in the question that mlmcc linked to, including the one in the first post by the person that asked the question, handle that by using the year from the datetime field.  For example, if the datetime in a record is in 2016, they calculate the desired dates in March and November for 2016.  As long as the dates when DST starts and end don't change again, or you're not looking at older dates, when the DST period was different, the formulas should work.

 Note that, as I mentioned in my latest post in that other question, none of those formulas include the time, so they'd default to midnight.  2 AM probably needs to be added to the start and end datetimes.

 James
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial