Datetime in Where clause

I have a simple stored procedure that updates the loggedout field, which is
a
datetime field, in a table based on the user id and login date (which is als
o
a datetime field). Since a user can login multiple times throughout the day,
I need the Where clause to include the time, which is part of the datetime
being passed in.
When I run the following sp and pass in a datetime field, for example
5/15/2005 1:12:22 PM, the table never updates, even though there is a field
with that date/time in it.
PROCEDURE dbo.UpdateLoginActivity
(
@.fldLogoutDate datetime,
@.fldUserId int,
@.fldLoginDate datetime
)
AS
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
How can I use a datetime field effectively in a Where clause, so that it
finds the exact row based on the date & time?datetime stores milliseconds:
select getdate()
---
2006-05-15 16:31:33.750
(1 row(s) affected)
YOu must either provide milliseconds in your parameter or modify your
update
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )|||It worked fine for me.
Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
PM ?
Richard wrote:
> I have a simple stored procedure that updates the loggedout field, which i
s a
> datetime field, in a table based on the user id and login date (which is a
lso
> a datetime field). Since a user can login multiple times throughout the da
y,
> I need the Where clause to include the time, which is part of the datetime
> being passed in.
> When I run the following sp and pass in a datetime field, for example
> 5/15/2005 1:12:22 PM, the table never updates, even though there is a fiel
d
> with that date/time in it.
> PROCEDURE dbo.UpdateLoginActivity
> (
> @.fldLogoutDate datetime,
> @.fldUserId int,
> @.fldLoginDate datetime
> )
> AS
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
> How can I use a datetime field effectively in a Where clause, so that it
> finds the exact row based on the date & time?|||That worked! Thanks Alexander.
"Alexander Kuznetsov" wrote:

> datetime stores milliseconds:
> select getdate()
> ---
> 2006-05-15 16:31:33.750
> (1 row(s) affected)
> YOu must either provide milliseconds in your parameter or modify your
> update
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
> AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )
>|||Yes, that is the correct date. See Alexander's post for the syntax, which
worked well for me. Thanks.
"tjolliffe@.hotmail.com" wrote:

> It worked fine for me.
> Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
> PM ?
>
> Richard wrote:
>