How to cast datetimeoffset to datetime in entity framework?

Question

How to cast datetimeoffset to datetime in entity framework?

datetimeoffset entity-framework-6

Question

It seems like a simple thing to do, but EF doesn't support properties like DateTimeOffset.DateTime or DateTimeOffset.LocalDateTime or even DateTime.Date. I'm trying to filter a DateTimeOffset type field by the DateTime component only (i.e. the 'local' DateTime), which is a simple thing to do in plain sql: where cast(x as datetime) = '2016-12-14'.

There is a DbFunctions.TruncateTime, but no corresponding TruncateOffset.

There just doesn't seem to be any way to cast or convert a DateTimeOffset object to a normal DateTime object that works in linq-to-entities. The only conversion is an implicit one from DateTime to DateTimeOffset, but not the other way around.

Triynko

View more on Stack Overflow

RMD · Accepted Answer · 9/23/2017 3:58:53 PM

You can use the DbFunctions.CreateDateTime to strip off the offset, for example:

...
.Select(e => new MyClass {                                      
  MyDate = DbFunctions.CreateDateTime(e.MyDateTimeOffset.Year,
                                      e.MyDateTimeOffset.Month,
                                      e.MyDateTimeOffset.Day,
                                      e.MyDateTimeOffset.Hour,
                                      e.MyDateTimeOffset.Minute,
                                      e.MyDateTimeOffset.Second)
...

As you might suspect, this creates some truly awful SQL, ie:

convert (datetime2,right('000' + convert(varchar(255), DATEPART (year, [Extent1].[MyDateTimeOffset])), 4) + '-' + convert(varchar(255), DATEPART (month, [Extent1].[MyDateTimeOffset])) + '-' + convert(varchar(255), DATEPART (day, [Extent1].[MyDateTimeOffset])) + ' ' + convert(varchar(255), DATEPART (hour, [Extent1].[MyDateTimeOffset])) + ':' + convert(varchar(255), DATEPART (minute, [Extent1].[MyDateTimeOffset])) + ':' + str( CAST( DATEPART (second, [Extent1].[MyDateTimeOffset]) AS float), 10, 7), 121) AS [C2]

However it will give you what you want.

One caveat is that this will completely strip-off / ignore the offset (as the OP requested).

How to cast datetimeoffset to datetime in entity framework?

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related