Remove Time Zone Offset from DateTimeOffset?

Question

This code:

DateTimeOffset testDateAndTime =
    new DateTimeOffset(2008, 5, 1, 8, 6, 32, new TimeSpan(1, 0, 0));

//CLEAN TIME AND DATE 
testDateAndTime = testDateAndTime.DateTime.Date; 

var datesTableEntry = db.DatesTable.First(dt => dt.Id == someTestId);
datesTableEntry.test= testDateAndTime;

db.SaveChangesAsync(); 

...produces this result in my database: 2008-05-01 00:00:00.0000000 -04:00

How should I revise my code so that it changes the time zone offset from -4:00 to +00:00 in testDateAndTime?

I have also tried:

public Task<DateTimeOffset> SetTimeZoneOffsetToZero(DateTimeOffset dateTimeOffSetObj)
{
    TimeSpan zeroOffsetTimeSpan = new TimeSpan(0, 0, 0, 0, 0);
    return dateTimeOffSetObj.ToOffset(zeroOffsetTimeSpan);
}

...but that code doesn't do anything.

My end goal is just to have a date without a time or a time zone offset. I do not want to convert the time to another time zone. (That is, I don't want to subtract 4 hours from the 00:00:00.0000000 time and remove set time offset to +00:00. I just want to set the offset to +00:00.)

Here is another approach that I came across elsewhere:

DateTimeOffset testDateAndTime =
    new DateTimeOffset(2008, 5, 1, 8, 6, 32, new TimeSpan(1, 0, 0));

testDateAndTime = testDateAndTime.DateTime.Date; //Zero out time portion

testDateAndTime = DateTime.SpecifyKind(
    testDateAndTime.Date, DateTimeKind.Utc); //"Zero out" offset portion

I was sure that SpecifyKind would convert my DateTimeOffset. That is, change both the time and the time zone offset. But, my test indicates that this code just changes the time zone offset, which is what I want. Is there a problem with doing it this way?

Answer 1

The issue doesn't have anything to do with the database actually. If you set a breakpoint or log the output somewhere, you should be able to see the offset being tacked on shortly after this code:

testDateAndTime = testDateAndTime.DateTime.Date;

Let's break this down:

• You started with a DateTimeOffset value of 2008-05-01T08:06:32+01:00
• You then called .DateTime, which resulted in a DateTime value of 2008-05-01T08:06:32 with DateTimeKind.Unspecified.
• You then called .Date, which resulted in a DateTime value of 2008-05-01T00:00:00 with DateTimeKind.Unspecified.
• You assign the result back to testDateAndTime, which is of type DateTimeOffset. This invokes an implicit cast from DateTime to DateTimeOffset - which applies the local time zone. In your case, it would appear the offset for this value in your local time zone is -04:00, so the resulting value is a DateTimeOffset of 2008-05-01T00:00:00-04:00, as you described.

You said:

End goal is just to have a date without time or time zone offset.

Well, there is currently no native C# data type that is just a date without a time. There is a pure Date type in the System.Time package in corefxlab, but that's not quite ready for the typical production application. There's LocalDate in the Noda Time library that you can use today, but you'd still have to convert back to a native type before saving to the database. So in the meantime, the best you can do is:

• Change your SQL Server to use a date type in the field.
• In your .NET code, use a DateTime with a time of 00:00:00 and DateTimeKind.Unspecified. You'll have to remember to ignore the time portion (as there are indeed dates without a local midnight in certain time zones).
• Change your test prop to be a DateTime, not a DateTimeOffset.

In general, while DateTimeOffset fits a large number of scenarios (such as timestamping events), it doesn't fit well for date-only values.

I want the current date, with zero offset.

If you really want this as a DateTimeOffset, you'd do:

testDateAndTime = new DateTimeOffset(testDateAndTime.Date, TimeSpan.Zero);

However, I advise against this. By doing so, you're taking the local date of the original value and asserting that it is in UTC. If the original offset is anything other than zero, that would be a false assertion. It is bound to lead to other errors later, as you're actually talking about a different point in time (with potentially a different date) than the one you created.

Regarding the additional question asked in your edit - Specifying DateTimeKind.Utc changes the behavior of the implicit cast. Instead of using the local time zone, it uses UTC time, which always has an offset of zero. The result is the same as the more explicit form I gave above. I still recommend against this, for the same reasons.

Consider an example of starting with 2016-12-31T22:00:00-04:00. By your approach, you'd save into the database 2016-12-31T00:00:00+00:00. However these are two very different points in time. The first one normalized to UTC would be 2017-01-01T02:00:00+00:00, and the second one converted to the other time zone would be 2016-12-30T20:00:00-04:00. Notice the change of dates in the conversion. This is probably not the behavior you'd want creeping into your application.