Posted By: CrystalGirl — 12 Mar 2019 at 2:09pm
hello,
How do I convert my current time (EST) to MST using formula and also handle the Daily Saving Time automatically?
Thank you
Edited by CrystalGirl - 12 Mar 2019 at 7:14pm
Posted By: kevlray — 13 Mar 2019 at 4:15am
I would assume that EST and MST would always have a two hour difference (Arizona does not observe Daylight Savings Time) all the time since they would switch between regular time and Daylight Savings Time at the same intervals.
Posted By: CrystalGirl — 13 Mar 2019 at 4:19am
Hi Kevlray,
Right now EST and MST has 3 hour different but in Oct it will be 2 hour different because of DST so that what I am struggling with now.
Posted By: kevlray — 13 Mar 2019 at 9:07am
Okay, I did not know that one time zone did not observe DST. I have seen a formula for TSQL to determine when DST starts and ends. I will have to do a search and determine if it can be converted to a Crystal Reports formula.
Posted By: CrystalGirl — 13 Mar 2019 at 9:31am
Sure, thank you kevlray
Posted By: kevlray — 13 Mar 2019 at 9:56am
So this is a start. I found the TSQL formula I was looking for. The two formulas below will give you the beginning and ending of DST. So then you will need to do a compare to the current date to determine whether to subtract two or three hours.
datetimevar StartOfMarch := dateadd('m',2, dateadd('yyyy', year(currentdate)-1900, date(1900,01,01)));
datetimevar DstStart := dateadd('h', 2, dateadd('d', ((15- datepart('w', StartOfMarch)) mod 7) + 7 , StartOfMarch));
dstStart
datetimevar StartOfNovember := dateadd('m',10,dateadd('yyyy',0,currentdate-1900));
datetimevar DstEnd := dateadd('h', 2, dateadd('d', ((8- datepart('w', StartOfNovember)) mod 7) , StartOfNovember)) ;
DstEnd
