Post reply

Local Date GMT +10

  • March 28, 2018 at 5:52 pm

    #334081

    Hi My Server is hosted in another country so the GetDate() returns the incorrect date for my local date, GMT+10 Eastern Coast of Australia. I have tried using AT TIME ZONE but it doesn't make a difference.
    Can anyone tell me how I fix this.

    SELECT CONVERT(date,GETDATE() AT TIME ZONE 'E. Australia Standard Time')

    --This is returning 2018-03-28 and should be 2018-03-29

    Thanks

  • March 28, 2018 at 6:21 pm

    #1984901

    GETDATE() returns a datetime data type, which lacks time zone information. The date provided from the server is treated as though it came from time zone offset +0:00 (GMT). Time zone stuff requires datetime2 to work correctly. Use SYSDATETIMEOFFSET() to return the current date/time as datetime2 that includes the time zone offset of the value, which can be properly converted with AT TIME ZONE.

    SELECT CONVERT(date,SYSDATETIMEOFFSET() AT TIME ZONE 'E. Australia Standard Time');

    Eddie Wuerch
    MCM: SQL

  • March 29, 2018 at 11:16 am

    #1985012

    What Eddie said. Stop using datetime and getdate()

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply