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.
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).