Why does my sql date comparison return 0 results

Question

I apologize in advance if this question is too long but I wanted to make sure I included all the steps I followed to get to this point.

I have the following table in my SQL Server 2008 database:

CREATE TABLE [VSPRRecalc](
    [VSPRDate] [datetimeoffset](7) NOT NULL,
    [CalcType] [int] NOT NULL,
CONSTRAINT [PK_VSPRRecalc] PRIMARY KEY CLUSTERED ([VSPRDate] ASC)

It has some rows in it that look like this:

INSERT [vsprrecalc](VSPRDate,CalcType) VALUES('2010-12-15 10:17:49.5780000 -05:00','3')
INSERT [vsprrecalc](VSPRDate,CalcType) VALUES('2010-12-16 07:44:03.3750000 -05:00','1')
INSERT [vsprrecalc](VSPRDate,CalcType) VALUES('2010-12-17 07:40:40.1090000 -05:00','1')
INSERT [vsprrecalc](VSPRDate,CalcType) VALUES('2010-12-18 16:29:02.2203744 -05:00','2')
INSERT [vsprrecalc](VSPRDate,CalcType) VALUES('2010-12-20 09:58:50.1250000 -05:00','1')
INSERT [vsprrecalc](VSPRDate,CalcType) VALUES('2010-12-29 19:21:26.8120000 -05:00','1')

I'm using linq to check and see if a given date already exists in this table:

var recalc = (from re in VSPRRecalcs
              where re.VSPRDate.Date == oDate.Value.Date
              select re).SingleOrDefault();

Currently recalc returns null whenever the date is within 5 hours of midnight (like the 12-29 case in the insert statements above). I checked and the following sql is being executed:

exec sp_executesql N'SELECT [t0].[VSPRDate], [t0].[CalcType]
                     FROM [dbo].[VSPRRecalc] AS [t0]
                     WHERE 
                     CONVERT(DATE, [t0].[VSPRDate]) = @p0',N'@p0 datetime',@p0='2010-12-29'

Which returns 0 records. I modified the query to make the test easier to play with and came up with the following:

declare @t as date
set @t =  '2010-12-29'

select *, 
case  when  CONVERT(DATE, [VSPRDate]) = @t then 'true' else 'false' end  
from VSPRRecalc where 
CONVERT(DATE, [VSPRDate]) = @t

That query works for any other date in the table but not for any date that is within 5 hours of midnight (again see 12-29 above). If I run the above query without the where clause the 12-29 row does have 'true' displayed so clearly the boolean is evaluating the way I expect in the select statement but not in the where clause. Why does that happen?

Weird, the above `executesql` statement **works** for me (it returns 1 row)... — rsenna, Jan 05 '11 at 15:37
... but, indeed, your last statement above is also not working for me (the `case` expression returns `true` for the 2010-12-29 row, but if I include the where part, no row is returned)! — rsenna, Jan 05 '11 at 15:44
Sorry that execute sql statement was left over from some testing I was doing. I've edited it now to reflect what should have been there. If you include the `and VSPRDate='2010-12-29 19:21:26.8120000 -05:00'` the query does work. I don't know what the difference is. — Mykroft, Jan 05 '11 at 15:47
Don't forget about the SqlMethods class which allows you to run SQL functions like DateDiffDay(start, finish) == 0, *on the database*. http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods.aspx — shaunmartin, Jan 07 '11 at 01:04

score 1 · Accepted Answer · edited May 23 '17 at 12:04

I would say that's a bug on SQL Server, regarding conversion between the DATETIMEOFFSET time and the more "standard" types DATETIME and DATE...

What I have find out is the following:

This works:

EXEC sp_executesql N'SELECT [t0].[VSPRDate], [t0].[CalcType]
                     FROM [dbo].[VSPRRecalc] AS [t0]
                     WHERE [t0].[VSPRDate] = @p0',

                     N'@p0 DATETIMEOFFSET(7)',
                     @p0 = '2010-12-29 19:21:26.8120000 -05:00'

Which means that when we keep using DATETIMEOFFSET, there is no problem whatsoever... Still, you seem to need to find all records in a given day, not search for an exact DATETIMEOFFSET, right?

So, probably a little bit more useful, this works also:

EXEC sp_executesql N'SELECT [t0].[VSPRDate], [t0].[CalcType]
                 FROM [dbo].[VSPRRecalc] AS [t0]
                 WHERE [t0].[VSPRDate] BETWEEN @p0 AND @p1',

                 N'@p0 DATETIMEOFFSET, @p1 DATETIMEOFFSET',
                 @p0 = '2010-12-29 00:00:00.0000000 -05:00',
                 @p1 = '2010-12-30 00:00:00.0000000 -05:00'

I guess the secret here is keep using the DATETIMEOFFSET data type (and it's CLR equivalent, System.DateTimeOffset). That way you will not get into this conversion issue...

(And, by the way, you should use a BETWEEN for searching records based on a date anyway. This allows the DBMS to use an index over that column, which is not possible when your WHERE clause is making a function call or a hard-coded conversion).

Edit I forgot there is no BETWEEN operator available for Linq for SQL - but that's easy to fix, just use something like WHERE [t0].[VSPRDate] >= @p0 AND [t0].[VSPRDate] <= @p1'... Also, this SO question is about declaring an extension method in order to implement it, but I don't know if it works...

I changed the Linq to be `VSPRDate >= oDate.Date && VSPRDate < oDate.Date.AddDays(1)`. This seems to work. — Mykroft, Jan 05 '11 at 22:02

Why does my sql date comparison return 0 results

1 Answers1