SELECT a datetimeoffset field by datetime

Question

I have a table with a datetimeoffset field which is primary key. I want to filter my table with a datetime.

For example my table has:

2016-04-27 23:30:00.7684500 +03:00
2016-04-28 00:30:00.7684500 +03:00

In local time format, first row mean 2016-04-28 02:30:00 and second row means 2016-04-28 03:30:00. I mean two of them in date: 2016-04-28.

When I wanted to report transactions in date 2016-04-28, I get only second row.

declare @fromDate datetime
select @fromDate = '2016-04-28 00:00:00'
select * from MYTABLE where dto > @fromDate

Because, sql look at UTC time in datetimeoffset field.

I can get what I really want like this:

declare @fromDate datetime
select @fromDate = '2016-04-28 00:00:00'
select * from MYTABLE where CAST(dto as datetime) > @fromDate

First and second row are coming.

The question is: does performance suffer due to casting? System looking and casting every row (sequential read), even if dto is the primary key?

Is there any better way?

Many thanks...

Answer 1

If your offset (+3) is a constant, you can try this:

declare @fromDate datetime
select @fromDate = '2016-04-28 00:00:00'
select * from MYTABLE where dateadd(hour, 3, dto) > @fromDate

Or you can use TODATETIMEOFFSET

as follow:

select * from MYTABLE where TODATETIMEOFFSET(dto, '03:00') > @fromDate 

Answer 2

Absolutely ... I would recommend adding a Calculated Field to the table. This way, the conversion takes place automatically in SQL and there is not CAST or CONVERT needed in the query. Do a search on Calculated Field and insert your code

CONVERT(DATETIME, dto, 109)

or

TRY_CONVERT(DATETIME, dto, 109)

I would use TRY_CONVERT as you can specifically set the format of the DateTime for your new calculated field and if for some reason, dto is not a valid date, it will insert NULL as opposed to failing.