I am having a problem with a datetime when passed as a paramater in a
procedure.

To demonstrate the problem I have created a small procedure which
selects from a view which has a datetime column.

The select is made twice, once using the parameter in the where clause
and once using a copy of the paramater in the where clause

Each select is the same the dates are the same, but the number of
records returned are different (see result of execing procedure below)

Seems like a bug to me but I can not find any reference to it
anywhere.

I have worked around this problem using a second variable.

Anybody know what causes this??

----------------------------------------------------------o--
o----------------------------------------------------
Alter  Procedure DateTestProc (@ForDate DateTime = Null) As

Declare @OtherDate datetime
Declare @RowCount Numeric(38)

Select @ForDate = Convert(DateTime, '2008-07-31', 121)

Select @OtherDate = @ForDate

Print Convert(Varchar(32), @ForDate, 121)
Print Convert(Varchar(32), @OtherDate, 121)

Select
    @RowCount = Count(*)
From
    TestView
Where
    TestDate = @ForDate

Print @RowCount

Select
    @RowCount = Count(*)
From
    TestView
Where
    TestDate = @OtherDate

Print @RowCount

Go
----------------------------------------------------------o--
o----------------------------------------------------
Exec DateTestProc

----------------------------------------------------------o--
o----------------------------------------------------

2008-07-31 00:00:00.000
2008-07-31 00:00:00.000
259377
315204

----------------------------------------------------------o--
o----------------------------------------------------