Monday, August 22, 2005

Storing a time zone value in SQL Server's datetime field

Q: Is there a way to store the time zone value in a datetime field in SQL Server? I receive the value of ‘12/1/2005 12:31:23 -5:00’ and when I try to store it in SQL it fails. Do I need to format the date/time differently, or I am forced to create a separate time zone field?

A: SQL Server does not support storing a timezone in a datetime field. However, to overcome that issue, you can convert all date/time values to the UTC timezone so you have a standard timezone to work with. When displaying the data to the user, you would then convert the time to the client’s timezone.

In regards to storing the value ‘12/1/2005 12:31:23 -5:00’ into SQL Server, try using the format '2005-08-09 00:00:00.000'.

No comments: