CODEWITHSUNDEEP
c#.netsql-serverwcfdatetime
Xavier W.
Xavier W.

Reputation: 1360

WCF - Store and restore UTC DateTime

We expose to our customer a WCF webservice allowing them to store DateTime in SQL Server databases. Extract of the WSDL : 

<xs:complexType name="TimePeriod">
<xs:sequence>
    <xs:element minOccurs="0" name="endDateTime" nillable="true" type="xs:dateTime"/>
    <xs:element minOccurs="0" name="startDateTime" nillable="true" type="xs:dateTime"/>
</xs:sequence>

For exemple, my customer send me UTC Datetime : 

<af:effectivePeriod>
   <af:startDateTime>2018-01-16T10:32:28Z</af:startDateTime>
</af:effectivePeriod>

This is stored in a SQL Server database in a datetime field.

But in the output of the read service, I don't have the UTC indicator : 

<af:effectivePeriod>
   <af:startDateTime>2018-01-16T10:32:28</af:startDateTime>
</af:effectivePeriod>

"Z" is kind of a unique case for DateTimes. The literal "Z" is actually part of the ISO 8601 DateTime standard for UTC times. When "Z" (Zulu) is tacked on the end of a time, it indicates that that time is UTC, so really the literal Z is part of the time

How is it possible for me to have the Z at the output of the read method? Do I have to modify the type of data stored in SQL Server? Do I have an impact in the WSDL of the services?

Upvotes: 1

Views: 387

Answers (2)

Richardissimo
Richardissimo

Reputation: 5773

Microsoft have an explanation of how to "round trip" dates and times when converting them to and from strings; and your problem (which I'll get to below) is that you've lost the Kind.

The DateTime ToString method's "o" standard format will give you the "round trip" kind that you are looking for.

Use DateTime Parse() with DateTimeStyles of RoundTripKind to read the value back.

The fact that your DateTime is missing the 'Z' indicator means (as documented on the Standard Format page for "o") that the DateTime's Kind is Unspecified. If you have read those DateTimes from your database, the Kind will be Unspecified (you can use a debugger to examine the Kind property after it has been read to confirm this).

The way around that is that you have to know what kind of DateTimes you are storing, so you can set the Kind when you read it (because if you don't do that, the Kind will be Unspecified). For example, if you only ever store UTC Kind DateTimes, then when you read them from your database, you can set the Kind on the DateTime appropriately. You can do that like this:

var myUtcDateTime = DateTime.SpecifyKind(myUnspecifiedDateTime, DateTimeKind.Utc);

I have an extension method to encapsulate this from me, so I can simply call:

var myDateTime = dataReader.GetUtcDateTime(ordinal);

which is implemented like this:

public static class DataReaderExtensions
{
    public static DateTime GetUtcDateTime(this IDataReader reader, int ordinal)
    {
        var readDateTime = reader.GetDateTime(ordinal);
        return DateTime.SpecifyKind(readDateTime, DateTimeKind.Utc);
    }
}

Or you can look at this answer to see how to do it with Entity Framework.

(Note that this approach works for UTC; but you can't decide to do the same thing with Kind of Local, since there's no guarantee that the local you are reading the value in is the same one that it was written in. For example, Daylight savings time may have just started or ended.)

But I have to say, if you're really interested in the actual times, the better solution to this kind of problem (if you forgive the pun!) is to use DateTimeOffsets. These store the DateTime and the Offset, and guarantee you get back out what you put in, without you needing to tinker with things.

Upvotes: 1

asidis
asidis

Reputation: 1494

Whenever you need to keep time offset you should work with DateTimeOffset (.net & SqlServer)

In your WCF datacontract, you can add a DateTimeOffset DataMember however it will have an impact on client side and your service wsdl will change as a complex type DateTimeOffset will be created: 

<xs:complexType name="DateTimeOffset">
    <xs:annotation>
        <xs:appinfo>
            <IsValueType>true</IsValueType>
        </xs:appinfo>
    </xs:annotation>
    <xs:sequence>
        <xs:element name="DateTime" type="xs:dateTime"/>
        <xs:element name="OffsetMinutes" type="xs:short"/>
    </xs:sequence>
</xs:complexType>

Upvotes: 1

Related Questions