Custom DateTime formats when using DataSet.WriteXml in .NET

Question

I've got an issue where I am writing a DataSet to XML that has a column of type DateTime and I want to control the output format.

DataSet data = LoadDataSet();
data.Tables[0].Rows[0]["MyDate"] = DateTime.Now;
data.WriteXml(myFile);

By default the format of the DateTime in the XML seems to be as follows:

2011-08-02T17:39:00-07:00

I'd like use a custom date format, or at the very least strip the timezone info.

Is there any way to control the format of the DateTime columns in my dataset XML?

My gut says no since I am assuming it is done this way to facilitate conversion of data across time zones but I've noticed that I can successfully read DataSet XML even if DateTime column tags omit the timezone data so I was hoping I can do something analogous when writing to XML.

Answer 1

This workaround might suit if you can live with just the timezone info getting stripped out. The default for the DateTimeMode property of DateTime columns in DataSets is UnspecifiedLocal. You could explicitly set the DateTimeMode to Unspecified, which means that the timezone part does not get serialized.

e.g.

You can use a function like this :

    public static void RemoveTimezoneForDataSet(DataSet ds)
    {
        foreach (DataTable dt in ds.Tables)
        {
            foreach (DataColumn dc in dt.Columns)
            {

                if (dc.DataType == typeof(DateTime))
                {
                    dc.DateTimeMode = DataSetDateTime.Unspecified;
                }
            }
        }
    }

Example of usage :

    DataSet ds = new DataSet();
    DataTable dt = new DataTable("t1");
    dt.Columns.Add("ID", typeof(int));
    dt.Columns.Add("DT", typeof(DateTime));
    dt.Rows.Add(new object[] { 1, new DateTime(2009, 1, 1) });
    dt.Rows.Add(new object[] { 2, new DateTime(2010, 12, 23) });

    ds.Tables.Add(dt);

    ds.WriteXml("c:\\Standard.xml");

    RemoveTimezoneForDataSet(ds);

    ds.WriteXml("c:\\WithoutTimezone.xml");

Output :

Standard.xml :

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <t1>
    <ID>1</ID>
    <DT>2009-01-01T00:00:00+11:00</DT>
  </t1>
  <t1>
    <ID>2</ID>
    <DT>2010-12-23T00:00:00+11:00</DT>
  </t1>
</NewDataSet>

WithoutTimezone.xml :

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <t1>
    <ID>1</ID>
    <DT>2009-01-01T00:00:00</DT>
  </t1>
  <t1>
    <ID>2</ID>
    <DT>2010-12-23T00:00:00</DT>
  </t1>
</NewDataSet>

If you don't like the idea of modifying the DataColumns of your original DataSet, you could make a copy of it, then call the function on the copy.

Answer 2

Apply XSLT transformation to DataSet: (from MSDN) (I believe you will find XSLT example to convert DateTime format, or see post on SO regarding XSLT and DateTIme formats)

DataSet custDS = new DataSet("CustomerDataSet");
XmlDataDocument xmlDoc = new XmlDataDocument(custDS); 

XslTransform xslTran = new XslTransform();
xslTran.Load("transform.xsl");

XmlTextWriter writer = new XmlTextWriter("xslt_output.html", 
  System.Text.Encoding.UTF8);

xslTran.Transform(xmlDoc, null, writer);
writer.Close();