CODEWITHSUNDEEP
sqlsql-serverdatetimetimezoneutc
user3637002
user3637002

Reputation: 87

Inserting a UTC offset datetime into SQL changes the time - Why?

I have a date 2015-03-11T08:43:19.7810000-05:00 the I am inserting into a MSSQL2008 database table datetime field.

I have verified my SQL Database is currently on daylight savings.

The East Coast of the USA is -5 offset.

Immediately upon inserting into SQL the time changes to 9:43am, adding one hour.

This would tell me some UTC or time zone conversion is not working.

I have even had my DBAs look at this and cannot figure it out.

I believe we should be storing datetime with UTC offset, but cannot figure out why this just started happening after the DST change last weekend.

Thanks.

Upvotes: 1

Views: 3080

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241693

If you want the offset persisted, you'll need to store it into a datetimeoffset field.

datetime fields do not save the offset. Because you provided one, it is applied but then the time is being normalized to the local time zone, which is in UTC-4 for the date in question.

Upvotes: 2

Related Questions