Re: Weird DST date calculation issue - RESOLVED
The suggestion below actually worked - except I changed it to this: DATEADD(day,-10,$My Date$) William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Blog: www.williamrentfrow.com O 715-592-5185 C 715-410-8056 -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Charles Baldi Sent: Tuesday, March 02, 2010 11:14 AM To: arslist@ARSLIST.ORG Subject: Re: Weird DST date calculation issue William, If you use the dateadd() function do you get a different result? E.g., dateadd(month, -10, date($My Date$)) Regards, Chuck Baldi On Tue, Mar 2, 2010 at 12:02 PM, William Rentfrow wrentf...@stratacominc.com wrote: ** Fortunately this issue SHOULD be very straight forward. Unfortunately - it isn't. There's a button that calculates a person's period of eligibility to make changes to their HR benefits, etc. You enter their employment anniversary date and hit the button and this performs a calculation: $My Date$ - 864000 (i.e., minus 10 days). Here's the interesting thing - when the date entered is Daylight savings time - 3/15 this spring - the calculated value for the date time field returns 3/4/2010 11:00:00 PM. Normally all of the times in this date/time field are left at 12:00:00 AM and are unused. Technically speaking the calculation is EXACTLY correct. 3/4/2010 11:00:00 PM is exactly 10 days before 3/15/2010 12:00:00 AM - because 3/15 has an extra hour added that is a figment of our collective imagination. Technically DST doesn't happen until 2:00 AM though but that's a matter for another time. I was thinking about changing the times on these to default to 3:00:00 AM instead of 12:00:00 AM - but I'm open to suggestions. William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Blog: www.williamrentfrow.com O 715-592-5185 C 715-410-8056 _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Weird DST date calculation issue - RESOLVED
Glad to hear it. Yeah, I meant to say day but we use month here so... Regards, Chuck On Thu, Mar 4, 2010 at 10:05 AM, William Rentfrow wrentf...@stratacominc.com wrote: The suggestion below actually worked - except I changed it to this: DATEADD(day,-10,$My Date$) William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Blog: www.williamrentfrow.com O 715-592-5185 C 715-410-8056 -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Charles Baldi Sent: Tuesday, March 02, 2010 11:14 AM To: arslist@ARSLIST.ORG Subject: Re: Weird DST date calculation issue William, If you use the dateadd() function do you get a different result? E.g., dateadd(month, -10, date($My Date$)) Regards, Chuck Baldi On Tue, Mar 2, 2010 at 12:02 PM, William Rentfrow wrentf...@stratacominc.com wrote: ** Fortunately this issue SHOULD be very straight forward. Unfortunately - it isn't. There's a button that calculates a person's period of eligibility to make changes to their HR benefits, etc. You enter their employment anniversary date and hit the button and this performs a calculation: $My Date$ - 864000 (i.e., minus 10 days). Here's the interesting thing - when the date entered is Daylight savings time - 3/15 this spring - the calculated value for the date time field returns 3/4/2010 11:00:00 PM. Normally all of the times in this date/time field are left at 12:00:00 AM and are unused. Technically speaking the calculation is EXACTLY correct. 3/4/2010 11:00:00 PM is exactly 10 days before 3/15/2010 12:00:00 AM - because 3/15 has an extra hour added that is a figment of our collective imagination. Technically DST doesn't happen until 2:00 AM though but that's a matter for another time. I was thinking about changing the times on these to default to 3:00:00 AM instead of 12:00:00 AM - but I'm open to suggestions. William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Blog: www.williamrentfrow.com O 715-592-5185 C 715-410-8056 _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Weird DST date calculation issue - RESOLVED
Lucky for you this worked. It doesn't work for me because I need to keep the time the same. (If someone wanted 3.15.2010 at 6:00pm, it has to stay that way) The DATEADD Function is great, but it resets the time back to 12:00:00 AM. I actually had to parse the data, set aside the time, add the days and then put it all back together again in the end. Works well, but I hate all the extra workflow. Lisa -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Charles Baldi Sent: Thursday, March 04, 2010 10:45 AM To: arslist@ARSLIST.ORG Subject: Re: Weird DST date calculation issue - RESOLVED Glad to hear it. Yeah, I meant to say day but we use month here so... Regards, Chuck On Thu, Mar 4, 2010 at 10:05 AM, William Rentfrow wrentf...@stratacominc.com wrote: The suggestion below actually worked - except I changed it to this: DATEADD(day,-10,$My Date$) William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Blog: www.williamrentfrow.com O 715-592-5185 C 715-410-8056 -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Charles Baldi Sent: Tuesday, March 02, 2010 11:14 AM To: arslist@ARSLIST.ORG Subject: Re: Weird DST date calculation issue William, If you use the dateadd() function do you get a different result? E.g., dateadd(month, -10, date($My Date$)) Regards, Chuck Baldi On Tue, Mar 2, 2010 at 12:02 PM, William Rentfrow wrentf...@stratacominc.com wrote: ** Fortunately this issue SHOULD be very straight forward. Unfortunately - it isn't. There's a button that calculates a person's period of eligibility to make changes to their HR benefits, etc. You enter their employment anniversary date and hit the button and this performs a calculation: $My Date$ - 864000 (i.e., minus 10 days). Here's the interesting thing - when the date entered is Daylight savings time - 3/15 this spring - the calculated value for the date time field returns 3/4/2010 11:00:00 PM. Normally all of the times in this date/time field are left at 12:00:00 AM and are unused. Technically speaking the calculation is EXACTLY correct. 3/4/2010 11:00:00 PM is exactly 10 days before 3/15/2010 12:00:00 AM - because 3/15 has an extra hour added that is a figment of our collective imagination. Technically DST doesn't happen until 2:00 AM though but that's a matter for another time. I was thinking about changing the times on these to default to 3:00:00 AM instead of 12:00:00 AM - but I'm open to suggestions. William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Blog: www.williamrentfrow.com O 715-592-5185 C 715-410-8056 _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ __ _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are __ _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Weird DST date calculation issue
Bill, It's taken me a long time to understand this issue. Still haven't figured out a good solution. The effect is because of the observance of DST when it changes. The issue is that you are subtracting a constant from the date. Where DST is observed, 2 days out of the year are not 24 hours. In the statements below I will reference dates in the US but the concept is the same for any where that observes DST. March 14th is a short day. That day is 23 hours in length. So 864000 is not exactly 10 days during that time period. 864000 translates to 10 days and 1 hour until after march 15th in your case. On November 7th this year you have the opposite effect. November 7th is a 25 hour day. 864000 would translate to 9 days and 23 hours until after November 17th. Hope this helps with the understanding of what is happening. Dave From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of William Rentfrow Sent: Tuesday, March 02, 2010 12:03 PM To: arslist@ARSLIST.ORG Subject: Weird DST date calculation issue ** Fortunately this issue SHOULD be very straight forward. Unfortunately - it isn't. There's a button that calculates a person's period of eligibility to make changes to their HR benefits, etc. You enter their employment anniversary date and hit the button and this performs a calculation: $My Date$ - 864000 (i.e., minus 10 days). Here's the interesting thing - when the date entered is Daylight savings time - 3/15 this spring - the calculated value for the date time field returns 3/4/2010 11:00:00 PM. Normally all of the times in this date/time field are left at 12:00:00 AM and are unused. Technically speaking the calculation is EXACTLY correct. 3/4/2010 11:00:00 PM is exactly 10 days before 3/15/2010 12:00:00 AM - because 3/15 has an extra hour added that is a figment of our collective imagination. Technically DST doesn't happen until 2:00 AM though but that's a matter for another time. I was thinking about changing the times on these to default to 3:00:00 AM instead of 12:00:00 AM - but I'm open to suggestions. William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Blog: www.williamrentfrow.comfile://www.williamrentfrow.com O 715-592-5185 C 715-410-8056 _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are