CR XI Custom Function to change the time zone of the field

building reports crystal reports
1

Hi,

I am trying to create a custom function to change the timezone of a field in the report depending on the timezone selected by the user in the prompt

I wrote a SQL Expression to do this , but i ended up creating a bunch of expressions for each time zone ,

So i am thinking of custom fuction some thing like

TimeZoneConvertor:

Fuction(TimeStampField , Timezone)

returns: Timestamp field with newtimezone as selected by user

Hope you get this and give me some kind of ideas to acheive this

Thanks in Advance!!!

Kavya

Kavya :india: (BOB member since 2007-07-16)

2

Not sure if this helps, but have you looked into using the ShiftDateTime function built into Crystal? It is pretty flexible and you can do a lot of different things with it.

JonTarz :us: (BOB member since 2006-01-09)

3

Thats exactly what i used , Thanks!!!

But could you help me on this, i can able to use this on most of the timezones , but this fuction can not able to convert to US/Arizona and
US/Indiana (East) Timezones because these two dont have daylight time saving

Could you give me sample code for converting CST to US/Arizona or US/Indiana (East)

For Central to Eastern Timezone , this works

ShiftDateTime (DataBaseField,‘CST,0,CDT’ ,‘EST,-60,EDT’)

Kavya :india: (BOB member since 2007-07-16)

4

Sure. I use the following to convert from local time zone (based on the Windows time zone setting) to UTC. Because of the switch this year, the function needed some additional If… Then logic. In this example I’m taking an end date based on the {?Date Range} parameter, adding enough hours to make it the equivalent of 23:59:59 and then checking to see what time period the date falls in. If it is prior to the whole DST change in 2007 it does one thing, else another.

DateTimeVar vEndDate;
vEndDate := (if HasUpperBound ({?Date Range}) = true then maximum({?Date Range}) else Date (2025, 12, 31));
vEndDate := vEndDate + 23.99999/24;
if year(vEndDate) <= 2006 and
   vEndDate <= datetime((date(year(vEndDate),4,1) + 7 - dayofweek(date(year(vEndDate),4,1)-1)), ctime("2:00 AM")) and
   vEndDate >= datetime(date(year(vEndDate),3,1) + 14 - dayofweek(date(year(vEndDate),3,1)-1), ctime("2:00 AM")) and

   Mid (DataTimeZone, InStr(InStr (1, DataTimeZone , ",") + 1, DataTimeZone, ",") + 1, 1) +
      Mid (DataTimeZone, InStr (InStr(InStr (1, DataTimeZone , ",") + 1, DataTimeZone, ",") + 1, DataTimeZone, " " ) + 1, 1) +
         Mid (DataTimeZone, InStr (InStr (InStr(InStr (1, DataTimeZone , ",") + 1, DataTimeZone, ",") + 1, DataTimeZone, " " ) + 1, DataTimeZone, " ") + 1, 1)
   in ["ADT", "MDT", "CDT", "EDT"]

then
    ShiftDateTime (vEndDate, "", "UTC,0") - (1/24)
else
    ShiftDateTime (vEndDate, "", "UTC,0")

JonTarz :us: (BOB member since 2006-01-09)

5

Thanks Jon,

I solved that issue .

Kavya

Kavya :india: (BOB member since 2007-07-16)