How can I convert UTC datetime to local (EST) time

  • I have a column called [created date ] that is of the datetime data type. This column has the date and time stored in UTC. About 13000 records are populated with this UTC date & time and I need to convert it to local time (EST).

    So, for example, the column has a value of '1/12/2004 7:04 PM' and I need it to read '1/12/2004 2:04 PM'.

    What is the easiest way to subtract 5 hours to convert these values to local time?

  • [created date] + GETDATE() - GETUTCDATE()

    _____________
    Code for TallyGenerator

  • Pat_B (1/24/2009)


    I have a column called [created date ] that is of the datetime data type. This column has the date and time stored in UTC. About 13000 records are populated with this UTC date & time and I need to convert it to local time (EST).

    So, for example, the column has a value of '1/12/2004 7:04 PM' and I need it to read '1/12/2004 2:04 PM'.

    What is the easiest way to subtract 5 hours to convert these values to local time?

    It's easy enough to subtract 5 hours from UTC time:

    DATEADD(HH,-5,MyUTCTime)

    The complication is in knowing that you might need to subtract 4 hours when it is daylight savings time and 5 hours when it isn't. For that, you will need some kind of lookup table or function to return the correct offset for different dates.

  • Thanks for the responses. Is it possible to use the UPDATE statement to subtract 5 hours from the UTC time, directly on the [created date] column like this:

    UPDATE table_name

    SET [created date] = DATEADD(HH,-5,[created date])

    WHERE some_column=some_value

  • Okay, I tested and the code from my last post worked well. So, I was able to directly update the column to subtract 5 hours from the UTC time.

    So, now I need to adjust the code to loop through all rows in the table. I will research this, but if anybody knows the answer, please post it because it will be quicker than me looking it up.

    Thanks for all your help!

  • I'm stuck on the looping part. Here's what I have so far:

    declare @count as int

    declare @stopcount as int

    set @count = 1

    select @stopcount = count(*) from tasks

    while @count <> @stopcount

    begin

    UPDATE tasks

    SET [opendate] = DATEADD(HH,-5,[opendate])

    WHERE wo_num = @count

    @count = @count + 1

    if @count = @stopcount

    break

    else

    continue

    end

    In this case, there are 13311 rows/records in the tasks table, and a PRINT @stopcount confirms this. However, I am getting this error when I try to run the query:

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '@count'.

    I don't understand why this isn't working.

  • I also tried this code based off of an example I found here (http://itknowledgeexchange.techtarget.com/itanswers/bulk-update-in-sql-server-2005/), but I'm getting the same "incorrect syntax near '@d' message.

    declare @count as int

    declare @d bit

    set @count = 1

    set @d = 1

    while @d = 1

    begin

    UPDATE tasks

    SET [opendate] = DATEADD(HH,-5,[opendate])

    WHERE wo_num = @count

    @count = @count + 1

    if @@rowcount = 0

    @d = 0

    end

  • I figured out why I was getting the syntax error near @count. I needed to place a SET in front of it. Anyways, the query ran, but my logic is flawed. I was performing the update/set where wo_num =@count, but I just noticed that there are gaps in the wo_num values. So, wo_num has values such as 1, 2, 3, then it skips to 5, 6, 7 and so on (probably because records were deleted). Anyways, I need a way to update all rows records in the table, regardless of a where condition. How can I accomplish this in relation to the code I have shown?

  • Looks like all I needed was the following code to update all rows (subtracted 5 hours from UTC time in the opendate column) in the tasks table.

    update tasks

    set opendate = DATEADD(HH,-5,[opendate])

  • Yes, that's it.

    If you don't need WHERE condition just don't add it.

    BTW, my script will work correctly not only in winter but in summer as well.

    And if they move the server to another time zone my solution will still work when the one you've chosen (why?) will need to be rewritten.

    _____________
    Code for TallyGenerator

  • Sergiy (1/24/2009)


    [created date] + GETDATE() - GETUTCDATE()

    That really only works correctly if the [created date] is from the current date.

    The OP posted an example date of '1/12/2004 7:04 PM' in his original post, so I assume that the table might contain different dates over a long period of time. If that is the case, he will probably need a lookup table that gives the correct UTC offset of different times in the past.

    Or it might be possible he is in some location that does not observe daylight savings time, in which case DATEADD(HH,-5,MyUTCTime) will work fine.

  • I chose this script because I am only going to be running this conversion query one time (today). Once I adjust the time, I intend to do a bulk update to transfer data from columns in one table to another. This time column just happened to be one of the columns I will be bulk updating.

  • If you are about to make bulk update of historical data then read again the post from Michael above.

    Time difference applied for summer months records should be different from time difference applied to winter months records.

    To manage it correctly you need to know when DTS started and finished every year and do UPDATE correspondingly.

    _____________
    Code for TallyGenerator

  • If you are in New York City, it's....

    update tasks
    set opendate = DATEADD(HH,-4,[opendate])

    Everyone has a plan until they get punched in the mouth. --Mike Tyson

Viewing 14 posts - 1 through 13 (of 13 total)

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