SQL Server 2005 error retrieving record based on Datetime primary key column

Question

Hi All,

I have a table in SQL Server 2005 with composite primary key, i.e. a foreign key (INT) and DateTime column.

When i run the SQL,

Select column1 From TABLE where ForeignKey=1 and DateTime='08/26/2010 11:24:36'

No record was return when there is such a record.

One option to do is

Select column1 From TABLE where ForeignKey=1 and DateTime>='08/26/2010 11:24:36' and DateTime<'08/26/2010 11:24:37'

But then, it just seems... Do i really have to resort to this? or it is because i have to specify until millisecond? Or is there any solutions?

Please help. Thanks in advance.

Answer 1

Yes, you would have to match the precision of the DateTime value to the millisecond for the equality to work.

Your proposed alternative will work. As another idea, you could use a cast and convert to truncate your stored datetime down to the second. Note however that using these functions in the where clause will negate the optimizer's ability to use any index on your datetime column. You'll have to decide what impact that would have in your specific environment.

/* CONVERT style 20 is yyyy-mm-dd hh:mi:ss */
Select column1 
    From TABLE 
    where ForeignKey=1 
        and CAST(CONVERT(char(19),DateTime,20) as datetime) = '08/26/2010 11:24:36'

EDIT: Following up on the comments below, I've noticed that SQL Server does not seem to pick up a 1 millisecond difference in a comparison. This is true in 2000/2005/2008 environments. If I use the new datetime2 datatype in 2008, then it works as expected. Reading up on the datetime datatype, the accuracy of the millisecond portion is rounded:

Accuracy: Rounded to increments of .000, .003, or .007 seconds

declare @datetime1 datetime
declare @datetime2 datetime
declare @datetime3 datetime
declare @datetime4 datetime

set @datetime1 = '2010-08-30 08:41:51.513'
set @datetime2 = '2010-08-30 08:41:51.513'
set @datetime3 = '2010-08-30 08:41:51.514'
set @datetime4 = '2010-08-30 08:41:51.515'

/* Expected result 'Equal'; Actual result 'Equal' */
if @datetime1 = @datetime2
    print 'Equal'
else
    print 'Not Equal'

/* Expected result 'Not Equal'; Actual result 'Equal' */    
if @datetime1 = @datetime3
    print 'Equal'
else
    print 'Not Equal'

/* Expected result 'Not Equal'; Actual result 'Not Equal' */    
if @datetime1 = @datetime4
    print 'Equal'
else
    print 'Not Equal'

Answer 2

If at all possible don't use a datetime field as your key. Instead create a different key and simply index your datetime value.