WCF - Store and restore UTC DateTime

Question

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?

How do you read data from the SQL server? I you control the formatting of the response you could perhaps use the `FORMAT` function - ex. `FORMAT(effectivePeriod, 'yyyy-MM-dd"T"HH:mm:ssZ')` — Kim Rasmussen, Jul 04 '18 at 11:28
I read data from SQL server using EntityFramework and the database first model — Xavier W., Jul 04 '18 at 11:43
How does the "read method" look like? Will it be possible to format the returned value in the WSDL? — Kim Rasmussen, Jul 04 '18 at 12:20
See [DateTime.Kind set to unspecified, not UTC, upon loading from database](https://stackoverflow.com/questions/40205893/datetime-kind-set-to-unspecified-not-utc-upon-loading-from-database/40349051#40349051). You can use the approach from the link **only if all** your `DateTime` values stored in the `datetime` database columns are supposed to be UTC. — Ivan Stoev, Jul 04 '18 at 13:13

asidis · Answer 1 · 2018-07-03T21:35:14.213

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>

And with the `DateTimeOffset` in WSDL and in DB, will I be able to have the date with the `Z` at the end automatically ? — Xavier W., Jul 04 '18 at 07:48

Richardissimo · Accepted Answer · 2018-07-05T16:41:46.687

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.

Work perfectly exactly like you told me. Thank you. Is there a way using dataAnnotations for example to force the user to send me UTC date in INPUT ? — Xavier W., Jul 09 '18 at 14:14
You could write a DataAnnotations ValidationAttribute to enforce that the `Kind` on that property must be `Utc`. — Richardissimo, Jul 09 '18 at 14:19
Alternatively just validate that it isn't `Unspecified`; and then you could call `ToUniversalTime()` on whatever DateTime value you're given before you store it (it won't change something which is already UTC). — Richardissimo, Jul 09 '18 at 14:52
And what do you think about the `[DateTimeKind(DateTimeKind.Utc)]` in the datacontract ? I haven't find a lot of documentation about it — Xavier W., Jul 10 '18 at 07:10
Sounds like a new question to me; but I haven't heard of that attribute, you'd need to give a link to what you're referring to. — Richardissimo, Jul 10 '18 at 09:20

WCF - Store and restore UTC DateTime

2 Answers2