January 24, 2009 at 10:30 pm
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?
January 24, 2009 at 11:52 pm
[created date] + GETDATE() - GETUTCDATE()
_____________
Code for TallyGenerator
January 25, 2009 at 12:21 am
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.
January 25, 2009 at 7:35 am
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
January 25, 2009 at 7:55 am
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!
January 25, 2009 at 9:58 am
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.
January 25, 2009 at 10:10 am
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
January 25, 2009 at 11:16 am
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?
January 25, 2009 at 11:57 am
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])
January 25, 2009 at 4:27 pm
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
January 25, 2009 at 8:44 pm
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.
January 25, 2009 at 8:54 pm
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.
January 26, 2009 at 3:48 pm
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
April 6, 2018 at 12:46 pm
If you are in New York City, it's....
update tasks
set opendate = DATEADD(HH,-4,[opendate])
The are no problems, only solutions. --John Lennon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply