I have a typed XML column in which I store the serialization from a C# object with a DateTime property. That DateTime is of Local type when I store it in the DB - although the serialized XML shows the time with an offset to GT time, as in '2009-09-22T13:52:32.2346252-07:00' (I live in Oregon). When I read the table (from within the SQL Management Studio), the XML shows the DateTime object in UTC type. Is there a way to control this behavior? It is important to me to respect the timezone of the data I put in, since it can come from different sources, and I need to respect that when I pull it out. I also do not want to convert every date time field from UTC to local after I read the objects from the DB.
this is the C# definition of the object I store:
[Serializable]
[XmlType(Namespace="urn://MySchema")]
public class MyObject
{
public DateTime RecordTime { get; set; }
// .. omitted for clarity
}
This is the definition of the table (simplified for clarity)
CREATE TABLE MyTable(
[ID] [int] IDENTITY(1,1) NOT NULL,
[MyKey] [int] NULL,
[TheObject] [xml](CONTENT [dbo].[MySchema]) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This is how my sproc looks like (also simplified)
ALTER PROCEDURE [dbo].[SaveMyObjects]
@where int,
@object xml
AS
BEGIN
INSERT INTO MyTable
(MyKey, TheObject)
VALUES(@where, @object)
END
I need to do 2 things differently:
- I want to control the format of the date string in the serialized XML - I'd prefer an ISO format for that time string
- I do not want to the DateTime to be converted to UTC when I store it in the DB, but I'd like the convenience of keeping it as a DateTime field.
How do I do that? Any idea, pointer, link to some god's blog showing my my errors, etc.. would be much appreciated.