C# DateTime has 'Z' when in memory, but not in SQL Db

Question

When I've got a DateTime object in memory, it gets output to the screen as something like this:

"2015-12-28T20:47:01.9382255Z"

After storing it in the SQL database and retrieving it, the 'Z' is dropped:

"2015-12-28T20:47:01.9382255"

I'm trying to keep all the dates on my server in UTC time. Do I have to set up the SQL column some special way to store that 'Z' character?

I want the 'Z' to be stored, I do not want it to be dropped from the SQL store.

Answer 1

Z means zulu time, which belongs on textual representation of a DateTime instance.

In Sql Server, neither datetime nor datetime2 types have time zone support. That's why you can't save this on those types. But datetimeoffset has time zone support.

But you need to insert DateTimeOffset instead of DateTime on such a case like;

var dto = DateTimeOffset.Parse("2015-12-28T20:47:01.9382255Z");

In Supported String Literal Formats for datetimeoffset section;

ISO Format - YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC)

Description - This format by ISO definition indicates the datetime portion should be expressed in Coordinated Universal Time (UTC). For example, 1999-12-12 12:30:30.12345 -07:00 should be represented as 1999-12-12 19:30:30.12345Z.

Looks like that's the only way if you really wanna see the Z in sql server.