SQLDateTime (Package DatabaseLink) with $TimeZone
- To: mathgroup at smc.vnet.net
- Subject: [mg123828] SQLDateTime (Package DatabaseLink) with $TimeZone
- From: ndrs <andreas.kohlmajer at gmx.de>
- Date: Wed, 21 Dec 2011 06:56:23 -0500 (EST)
- Delivered-to: l-mathgroup@mail-archive0.wolfram.com
Hello! Is there a simple solution to use $TimeZone together with SQLDateTime to write to the SQL data type datetimeoffset? Example: SQL (MS SQL 2008) database [TestDB] with a table [TestData], having only one column [Timestamp1] declared as datetimeoffset(2). This stores a timestamp, together with the time zone information and a precision of max. 2 decimals of a second, e.g. SELECT CONVERT(DATETIMEOFFSET(2),SYSDATETIMEOFFSET()) gives "2011-12-20 09:40:16.42 +01:00" (current time in Germany). Reading this column via package DatabaseLink gives SQLDateTime[{2011, 12, 20, 9, 40, 16.42}]. If I write SQLDateTime[{2011, 12, 20, 9, 40, 16.42}] back to the database, I get "2011-12-20 09:40:16.42 +00:00", so the time zone information is lost - more precisely: it has not been transmitted, I think. Is there a way to use $TimeZone, that the result would be "2011-12-20 09:40:16.42 +01:00"? Thanks for your help!