CODEWITHSUNDEEP
c#asp.netsql-serverdatetime
waffles
waffles

Reputation: 1396

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

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.

Upvotes: 3

Views: 1731

Answers (2)

Soner Gönül
Soner Gönül

Reputation: 98840

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.

Upvotes: 4

profesor79
profesor79

Reputation: 9473

datetime field is OK. The Z is there because

If the time is in UTC, add a 'Z' directly after the time without a space. 'Z' is the zone designator for the zero UTC offset. "09:30 UTC" is therefore represented as "09:30Z" or "0930Z". "14:45:15 UTC" would be "14:45:15Z" or "144515Z".

Upvotes: 0

Related Questions