Post reply

convert time for daylight saving ..UTC to EST

  • August 11, 2021 at 5:15 pm

    #3918875

    Hi

    i would like to  convert UTC to EST .

    currently i am using "dateadd(hour, -4, UTC_COLUMN)" , but it will change when day light saving changes in november.

    Again i will need to update my script with "dateadd(hour, -5, UTC_COLUMN)" .

    is there any fixed script for this ?

    Any suggestion.

     

    Thank you !

  • August 11, 2021 at 5:19 pm

    #3918878

    AT TIME ZONE clause.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • August 17, 2021 at 1:30 am

    #3920562 

    Select CAST((CONVERT(DATETIMEOFFSET, GETDATE()) AT TIME ZONE 'EASTERN Standard TIME') AS DATETIME)

  • August 17, 2021 at 4:30 am

    #3920596

    I've attached 3 functions for this that I needed to build for a project at the end of July.  They're fully documented and they include test code for your QA/UAT people to verify with.  One of the functions actually reads the registry of the machine that it's being executed from to get the local time zone (according to Windows) so you might might not have to change any code when you deploy to machines in different time zones.

    The file has been attached as a TXT file.  Once you download it, change the extension to SQL, load it into SSMS, do a safety review and functionality check on the code (I am not responsible for any use of the code and it is presented "AS IS").  If you make a lot of $$$ with it, send me 1% of your profits so I can finally retire. 😀

    And forgive the misspelling of UDR, which should have been UDF and I missed it in one  of the cmments 'till just now  😉

    The code is about as short as what the folks above posted.  The documentation is huge because a whole lot of people have a lot of difficulty with it.

     

    Attachments:
    You must be logged in to view attached files.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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