January 28, 2011 at 7:40 pm
I've been having to deal with UTC dates quite heavily the last week. VERY humbling.
I would like to write a simple insert script to that can correctly insert a date into a DateTime2 column regardless of what timezone the server is in. This is very tricky business, at lest for me. This is the closest thing I could come up with, but it seems to behave differently on my local SQL Server vs. Azure SqlServer. My assumption here would be that no matter where you ran this script, SSMS would recognize the 8AM local time on the workstation, and save it "utc adjusted" on any server. I don't think that's what's happening though. Any thoughts?
declare @ThisDate DateTime2
declare @UtcOffset int
set @UtcOffset = datediff(hour, getdate(), getutcdate())
set @ThisDateTime = '20110303 08:00:00 AM'
insert into TestTable(TestDate) values(dateadd(hour, @UtcOffset, @ThisDateTime))
Thanks as always!
.
January 28, 2011 at 11:45 pm
The script executes on the SQL server, not the client, and SQL Server has no knowledge of the timezone setting of the client, so the time of getdate() will be from the SQL Server.
If you want to insert the clients local time, you will have to send the time to the server from an application that gets the time from the client.
January 29, 2011 at 1:03 pm
So I guess the old saying is true then: "no matter where you go, there you are". I guess I should always express litteral times in UTC format, regardless of the sever/client/etc.
.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply