Fetch Localized Date Time

[Former Member]

Hi All,

If MII Server setup is at UTC while the application is deployed at multiple locations with varied time zone. What is the best approach to convert the date time zone to localized format and pass it to ECC.

I have seen couple of functions like

difftimezone(loc, dest),

dstoffset(tz), datenow.. etc.

Is there any standard way which we follow to overcome this kind of issue's arising via time zone difference..( I was thinking like since we have many functions around this, maybe we have some expression which helps in overcoming this issue along with accounting day time saving)

Something standard like have MII in UTC and if deployed under local -CST/EST time zone.. this is valid expression as a standard to be followed to convert /account time zone difference...

Thanking you in advance for your time and effort. Looking forward to your reply.

Regards

Ruchir

[Former Member]

Hi Ruchir,

Can you please highlight on the function used by you? Even I have a similar scenario and I wanted to use some standard function.

Regards,

Niyati

[Former Member]

Thanks Rohit.. Thanks Tufale..

Your reply was helpful.. Using the standard built in functions - i was able to achieve the transformation I was looking in!

Regards
Ruchir

[former_member211944]

Hi Ruchir,

Which function did you use?

Regards,

Rohit Negi.

[Former Member]

Hi Rohit,

I used the standard functions like listed above to complete the requirement at my end.

Regards

Ruchir

[Former Member]

Hello Ruchir,

    If I had a requirement like this, then I would go for some thing like

Check Martin's reply in the above post. Building a custom action for a complex yet day to day scenario is a better idea I believe.

Regards

Tufale Ashai.

[Former Member]

Thanks Anuj .. Thanks Hari...

I was looking for a standard approach / expression created via MII build in functions which could help in convertion so that we need not bother about time zone diff/ daylight saving etc..

I understood approach you guys took .. They are good but slightly differs.. I was able to use standard datetime functions and tweet them based on my needs..

Thank you for helping in and sharing your thoughts.. Appreciate your time and effort!

Regards

Ruchir

[former_member211944]

Hi Ruchir,

I found this function which could be useful to you:

converttimezone(date, tz)

Returns the date in the given time zone.

DateTime

Assume the server is in the EST time zone: converttimezone("2000-01-01T00:00:00", "PST") returns 1999-12-31T1:00:00.PST.

The date parameter must be a valid date and time. The tz parameter must be a valid time zone.

This is just the screen shot from the help doc of MII.

Regards,

Rohit Negi.

[HariCS23]

Hello Ruchir, I had to deal with converting from GMT to MT ( MDT ,MST ) lately...

At last ..fool proof way i found to convert GMT to local time ( MT ) is to write custom code to find offset based on the current date.

But i had to hard code day light time period for about 15 years  ( which was enough for my requirement) to calculate offset hours.

Hope below code helps.. ( BTW ...this is PL/SQL code ) ..you can implement this in javascript too...

set @OFFSETHRS =

          (select

                    case

                              when (@DBDATE between (cast('2013-03-10 02:00:00.000' AS datetime)) and (cast('2013-11-03 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2014-03-09 02:00:00.000' AS datetime)) and (cast('2014-11-02 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2015-03-08 02:00:00.000' AS datetime)) and (cast('2015-11-01 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2016-03-13 02:00:00.000' AS datetime)) and (cast('2016-11-06 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2017-03-12 02:00:00.000' AS datetime)) and (cast('2017-11-05 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2018-03-11 02:00:00.000' AS datetime)) and (cast('2018-11-04 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2019-03-10 02:00:00.000' AS datetime)) and (cast('2019-11-03 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2020-03-08 02:00:00.000' AS datetime)) and (cast('2020-11-01 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2021-03-14 02:00:00.000' AS datetime)) and (cast('2021-11-07 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2022-03-13 02:00:00.000' AS datetime)) and (cast('2022-11-06 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2023-03-12 02:00:00.000' AS datetime)) and (cast('2023-11-05 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2024-03-10 02:00:00.000' AS datetime)) and (cast('2024-11-03 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2025-03-09 02:00:00.000' AS datetime)) and (cast('2025-11-02 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2026-03-08 02:00:00.000' AS datetime)) and (cast('2026-11-01 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2027-03-14 02:00:00.000' AS datetime)) and (cast('2027-11-07 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2028-03-12 02:00:00.000' AS datetime)) and (cast('2028-11-05 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2029-03-11 02:00:00.000' AS datetime)) and (cast('2029-11-04 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2030-03-10 02:00:00.000' AS datetime)) and (cast('2030-11-03 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2031-03-09 02:00:00.000' AS datetime)) and (cast('2031-11-02 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2032-03-14 02:00:00.000' AS datetime)) and (cast('2032-11-07 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2033-03-13 02:00:00.000' AS datetime)) and (cast('2033-11-06 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2034-03-12 02:00:00.000' AS datetime)) and (cast('2034-11-05 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2035-03-11 02:00:00.000' AS datetime)) and (cast('2030-11-04 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2036-03-09 02:00:00.000' AS datetime)) and (cast('2036-11-02 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2037-03-08 02:00:00.000' AS datetime)) and (cast('2037-11-01 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2038-03-14 02:00:00.000' AS datetime)) and (cast('2038-11-07 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2039-03-13 02:00:00.000' AS datetime)) and (cast('2039-11-06 02:00:00.000' AS datetime)) or

                                                  @DBDATE between (cast('2040-03-11 02:00:00.000' AS datetime)) and (cast('2040-11-04 02:00:00.000' AS datetime)))

                                        then 

                                                  -6

                              else

                                                  -7

                    end)

SET @DCDATE = (SELECT DATEADD(hour, @OFFSETHRS, @DBDATE))

Thanks

Hari

[Former Member]

Hi Ruchir,

I am not sure if this will be a good approach and how full proof will it be but just to add one pointer as some time back I had played around this thing.

How about passing the date time from javascript (var myDate = new Date()) because it takes the date, time  and time zone of the computer on which the page is being run in UTC format which we can convert to the required DateTime format.

Thanks & Regards,

Anuj