none
Calculate GMT + and GMT -

    Question

  • How to Store GMT  values in table

    for example i stored the below values on my table

    countryname             differenceInminutes

    INDIA                        5.5
    Australia                    9.5
     IRAN                        4.5
    Tokyo                        9

    (

    select dateadd(minute, 60 * DifferenceInMinutes, getutcdate()) from CountryTimes where CountryName = 'LONDON')


    if i want to calculate GMT for UK ...How to store the GMT in my table...
    now i am storing only GMT+ values only..
    How to store GMT - values ?



    Saturday, July 25, 2009 6:32 AM

Answers

  • yes ..But it shows wrong time...


    It returns
    2009-07-25 01:20:54.580

    But in London the time is 10:22 AM

     

     


    1. What is your time zone ? GMT+8 ?
    2. Can you also show us your local time, "But in London the time is 10:22 AM " does not tell us much at all.


    select     getdate(), getutcdate(), DifferenceInMinutes, dateadd(minute, 60 * DifferenceInMinutes, getutcdate())
    from     CountryTimes
    where     CountryName = 'LONDON'
    getutcdate() already give you UTC/GMT time. London is at GMT+0 (standard time not considering DST). So your table should be

    countryname             differenceInminutes
    
    INDIA                        5.5
    Australia                    9.5
     IRAN                        4.5
    Tokyo                        9
    London                    0.0




    KH Tan
    • Marked as answer by goms Saturday, July 25, 2009 10:09 AM
    Saturday, July 25, 2009 9:59 AM
  • It returns
    2009-07-25 01:20:54.580

    But in London the time is 10:22 AM
    That is because the current London UTC offset is +1.  Also, London observers British Summer Time (BST) only in the summer months.  On October 25, 2009 at 2:00 AM BST, London will switch back to GMT (UTC +0).
     
    To convert UTC to a local time, you need to factor in not only time of day, but also the date and laws of the specific locale.  Many places have the ugly habit of changing clocks a couple of times a year as law dictates.  The date these changes occur can change due to new legislation.  See http://en.wikipedia ..org/wiki/Time_in_Indiana for an example of such disfunctionllity.

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    • Marked as answer by goms Monday, July 27, 2009 3:33 AM
    Saturday, July 25, 2009 1:44 PM

All replies

  • What is the difference between GMT+ and GMT- ?
    Saturday, July 25, 2009 7:07 AM
  • use negative value
    KH Tan
    Saturday, July 25, 2009 8:10 AM
  • (GMT – 8:00) GMT
    (GMT +5.5)GMT


    How to store (GMT – 8:00) GMT in table
    How sqlserver calculate this?

    Saturday, July 25, 2009 9:08 AM
  • Did you try this?


    countryname             differenceInminutes

    INDIA                        5.5
    Australia                    9.5
     IRAN                        4.5
    Tokyo                        9
    London                    -8.0
    Saturday, July 25, 2009 9:16 AM
  • yes ..But it shows wrong time...


    It returns
    2009-07-25 01:20:54.580

    But in London the time is 10:22 AM

     

     

    Saturday, July 25, 2009 9:24 AM
  • yes ..But it shows wrong time...


    It returns
    2009-07-25 01:20:54.580

    But in London the time is 10:22 AM

     

     


    1. What is your time zone ? GMT+8 ?
    2. Can you also show us your local time, "But in London the time is 10:22 AM " does not tell us much at all.


    select     getdate(), getutcdate(), DifferenceInMinutes, dateadd(minute, 60 * DifferenceInMinutes, getutcdate())
    from     CountryTimes
    where     CountryName = 'LONDON'
    getutcdate() already give you UTC/GMT time. London is at GMT+0 (standard time not considering DST). So your table should be

    countryname             differenceInminutes
    
    INDIA                        5.5
    Australia                    9.5
     IRAN                        4.5
    Tokyo                        9
    London                    0.0




    KH Tan
    • Marked as answer by goms Saturday, July 25, 2009 10:09 AM
    Saturday, July 25, 2009 9:59 AM
  • It returns
    2009-07-25 01:20:54.580

    But in London the time is 10:22 AM
    That is because the current London UTC offset is +1.  Also, London observers British Summer Time (BST) only in the summer months.  On October 25, 2009 at 2:00 AM BST, London will switch back to GMT (UTC +0).
     
    To convert UTC to a local time, you need to factor in not only time of day, but also the date and laws of the specific locale.  Many places have the ugly habit of changing clocks a couple of times a year as law dictates.  The date these changes occur can change due to new legislation.  See http://en.wikipedia ..org/wiki/Time_in_Indiana for an example of such disfunctionllity.

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    • Marked as answer by goms Monday, July 27, 2009 3:33 AM
    Saturday, July 25, 2009 1:44 PM