Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: ShiftDateTime DST issues Post Reply Post New Topic
<< Prev Page  of 5 Next >>
Author Message
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 29 Feb 2012 at 4:18am
Originally posted by turdferguson

It's more than just simple dateadd formulas. You have to check second sundays for both November and March.

Even moreso, you'll have to somehow check if its in an area that observes DST, and without a param or a DB value, I'm not sure how that's going to be done. But the big issue is why Crystal doesnt support DST when converting from UTC.
 
He only needs to do it for one timezone, and since he's making remarks towards DST I'd say it's safe to assume DST applies. The following formula should get people start at identifying DST periods and applying adjustments based on them.
 
@Formula
datetimevar datecheck;
datetimevar dststart;
datetimevar dstend; 


datecheck:= {table.datefield} 


dststart:= dateadd("d",8-dayofweek(date(year(datecheck),03,07)),date(year(datecheck),03,07)); 


dstend:= dateadd("d",8-dayofweek(date(year(datecheck),11,07)),date(year(datecheck),11,07)); 


if datecheck in dststart to dstend 


then "DST Period"
//then dateadd("h",3,datecheck) // +3 hours to {table.datefield} 


else "Not DST Period"
//else dateadd("h",2,datecheck) // +2 hours to {table.datefield}
 
Regards,
Ryan.


Edited by rkrowland - 29 Feb 2012 at 5:06am
IP IP Logged
turdferguson
Newbie
Newbie


Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
Quote turdferguson Replybullet Posted: 29 Feb 2012 at 4:23am
Originally posted by rkrowland

Originally posted by turdferguson

It's more than just simple dateadd formulas. You have to check second sundays for both November and March.

Even moreso, you'll have to somehow check if its in an area that observes DST, and without a param or a DB value, I'm not sure how that's going to be done. But the big issue is why Crystal doesnt support DST when converting from UTC.
 
He only needs to do it for one timezone, and since he's making remarks towards DST I'd say it's safe to assume DST applies. The following formula should get people start at identifying DST periods and applying adjustments based on them.
 
@Formula
datetimevar test;
datetimevar dststart;
datetimevar dstend;


 


test:= dateadd("yyyy",-1,currentdate()); // Change to date field...


 


dststart:= dateadd("d",8-dayofweek(date(year(test),03,08)),date(year(test),03,08));



dstend:= dateadd("d",8-dayofweek(date(year(test),11,08)),date(year(test),11,08));


 


if {table.datefield} in dststart to dstend



then "DST Period"
//then dateadd("h",3,{table.datefield})



else "Not DST Period"
//else dateadd("h",2,{table.datefield})
 
Regards,
Ryan.


The rest of his post goes on to say that "As more companies run global operations this will become more of a problem." Ours is global, thus why this is an issue.

But thanks for the formula. It's a bit different from how I started ours but much better, but one question. 

test:= dateadd("yyyy",-1,currentdate()); // Change to date field...

I dont quite understand what you're accomplishing here.


Edited by turdferguson - 29 Feb 2012 at 4:24am
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 29 Feb 2012 at 4:25am
I did that to check my dststart and dstend formulas were working for last year aswell as this year - that var should just refer to the datefield in question. 
 
Regards,
Ryan.
 
EDIT: Note, the dststart and dstend formulas won't work for every scenario in the above example. My brain's a little fried at the minute but the following should work better;
 
dststart:= dateadd("d",8-dayofweek(date(year(test),03,07)),date(year(test),03,07));
dstend:= dateadd("d",8-dayofweek(date(year(test),11,07)),date(year(test),11,07));


Edited by rkrowland - 29 Feb 2012 at 4:35am
IP IP Logged
turdferguson
Newbie
Newbie


Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
Quote turdferguson Replybullet Posted: 29 Feb 2012 at 5:34am
Originally posted by rkrowland

I did that to check my dststart and dstend formulas were working for last year aswell as this year - that var should just refer to the datefield in question. 
 
Regards,
Ryan.
 
EDIT: Note, the dststart and dstend formulas won't work for every scenario in the above example. My brain's a little fried at the minute but the following should work better;
 
dststart:= dateadd("d",8-dayofweek(date(year(test),03,07)),date(year(test),03,07));
dstend:= dateadd("d",8-dayofweek(date(year(test),11,07)),date(year(test),11,07));


My brain isnt at 100% today... why are you subtracting 8-7 for the dateadd? It that finding the second sunday of the month somehow? Or can you explain it for poor little ol me?
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 29 Feb 2012 at 5:41am
I'll explain the dststart and hopefully you'll be able to figure out dstend from that :)
 
dateadd("d",8-dayofweek(date(year(test),03,07)),date(year(test),03,07))
 
date(year(test),03,07)) = perform calculations on date 07-March-year, we start with this date as the second Sunday within any month must fall between the 8th to the 14th.
 
The dayofweek part calculates what day of the week the 7th of march was in the corresponding year. ie if the 7th was a Monday, it would return 2.
 
From that we can work out what date the second Sunday in that month/year was.
 
The 8 - dayofweek is the number of days we need to add to the 7th of March to get to a Sunday. So let's go with our Monday example above 8-2 = 6, 6days + 7th = 13th. Which means the second Sunday in March that year fell on the 13th.
 
We do the same for November and check to see if our date falls between both of them.
 
Hope that helps.
 
Regards,
Ryan.


Edited by rkrowland - 29 Feb 2012 at 5:41am
IP IP Logged
turdferguson
Newbie
Newbie


Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
Quote turdferguson Replybullet Posted: 29 Feb 2012 at 5:49am
Interesting! I was thinking it straight up subtracted from the 03/07/year... and that you meant to mean 8+ instead of 8- dayofweek to get the second sunday...

Thanks Ryan!
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 29 Feb 2012 at 6:20am
ok, looked up daylight saving times in wikipedia...Ryan's solution won't work for every where.  Why, not every country starts / stops their DST on the same day.
 
And because of the friggin complexity that every country/state may use or not DST and that the start/end of DST varies between countries would be nightmare to code / maintain, especially since each country can change the date that the start/stop DST...
 
last note, according to Wikipedia, Australia ends DST the first Sunday in April...not starts on the 2nd Sunday of March...just another complication, since the southern hemisphere is the reverse of the northern...


Edited by lockwelle - 29 Feb 2012 at 6:23am
IP IP Logged
edmhess
Newbie
Newbie


Joined: 29 Feb 2012
Online Status: Offline
Posts: 2
Quote edmhess Replybullet Posted: 01 Mar 2012 at 4:36am

I'm going to use a subreport for my headers that has the date/time and use DateAdd("h", 16, CurrentDateTime) for Melbourne.  I want to replace the 16 with a system parameter that could be set by an admin whenever there are DST changes in PA or Melbourne.  I don't like it but I don't want the end-users to have to enter an offset every time they run a report.

It looks like the report is getting the Company Name with SystemParamCompanyName1 : ?SystemParam_Company_Name.  I can't find anything in the online help about system parameters and don't know how to set them up.
 
Thanks.
IP IP Logged
turdferguson
Newbie
Newbie


Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
Quote turdferguson Replybullet Posted: 02 Mar 2012 at 10:38am
I've now run into a new problem with the function given to me.

We've been testing against EST -5:00GMT and PST -8:00GMT (if i got the acronyms wrong, whatever).

When we put the following through EST, we get an issue, and only on this specific case. Normal cases outside of dates residing alongside the DST change are fine.

1. On 3/11/2012, at 9:59:59AM UTC, it returns 4:59:59AM EST. This is correct, and outside DST so a five hour difference is correct. When converting to pacific time on my machine, we should see 1:59:59AM, since it's an eight hour difference. Instead, I get 12:59:59AM.


ShiftDateTime (testdate, ",0,UTC","")  //Convert to the user's current timezone.

datetimevar test;
datetimevar dststart;
datetimevar dstend;

test:= testdate;

dststart:= dateadd("d", 8 - dayofweek(date(year(test),03,07)), date(year(test),03,07));

dstend:= dateadd("d", 8 - dayofweek(date(year(test),11,07)), date(year(test),11,07));
 
if test in dststart to dstend
then dateadd("h",0,test)
else dateadd("h",1,test)
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 04 Mar 2012 at 11:38pm
Ok is that the 11th of March or the 3rd of November?
 
You'll have to forgive me, I hate the American date format haha! Common date format in England is dd/mm/yyyy - which just makes it confusing when looking at American dates! ;-) I always try to post dates in dd/mmm/yyyy when using forums with multiple nationalities just to make things easier!
 
I'm going to assume it's 11-Mar-2012 - if so the problems are most-definetely caused by the fact that 11-Mar-2012 is the startdate of our DST calculation - however I'm not entirely sure what's causing it as none of the time calculations decrease/increase the hours enough for it to be listed on 2 separate days.
 
Perhaps try applying our DST calculation after you've changed the defaul timezone hours. IE change
 
test:= testdate;
to
 
test:= ShiftDateTime (testdate, ",0,UTC","");
 
Again I'm not familiar with the shiftdatetime function and I'd probably find a way of using dateadd to do this part too, however if you're comfortable with it's functionality go nuts! ;-)
 
Regards,
Ryan.
IP IP Logged
<< Prev Page  of 5 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.012 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.