error msg "cant convert data type datetime to type int".

[newtosql]

create procedure sp_display_activity @loginid int, @date datetime
as
select * from LoginActivity
where [date] = @date
and loginid = @loginid

Im having difficulty returning values from my stored procedure
that accepts 2 parameters of "date" and "loginID". I get an
error msg "cant convert data type datetime to type int". I
would like to pass a date parameter of just 12/01/2004 or
12-02-2004 and ignore any timestamp data in the field.

Im new to sql server and dont know the syntax I need to use.
Can anyone help me? Thanks.

 

[SQLBill]

Does this work?

In your create procedure, before the SELECT add:
SET DATEFORMAT mdy

Now run:

EXEC sp_display_activity <loginid>, '12/02/2004'

-SQLBill

 

[newtosql]

Hmmm this doesnt work. In the table, my date column is datetime datatype and displays dates/times as &quot;12/01/2004 12:00:00&quot;. I want my user to type only &quot;12/01/2004&quot; into the stored procedures date parameter and for the procedure to display results without having to specify the &quot;00:00:00&quot; part of the datetime datatype.

Cheers.

 

[fluteplr]

What are you using (front end) to pass the parameter to sql server. I suspect it is doing something to your value before sql server sees it.

 

[eramgarden]

before your select, can you convert the user's input and add the time to it? Then before u display the result, chop off the time part and only display the date...

 

[checkai]

That error seems like you aren't putting quotes around your date. Could that be?
dlc

 

[newtosql]

The user will type the date parameter in query analyzer. Im not sure whats going wrong as Im entering my date parameter with quotes around it. How would I convert the users input and add the time to it?

 

[fluteplr]

Don't let your users run query analyser.

It is not designed for normal users.

If they leave the quotes out they are always going to get an error.

 

[newtosql]

Ok, I havent released this to users yet as I cannot get it working myself! lol. I AM placing quotes around the date tho.

To explain further I have in my table:

Login Date Name
---------------------------------------
1433 12/01/2004 12:15:02 Smith

I want to write a stored procedure that will accept two parameters of login and date and then display all columns in the table. I dont want to enter the time in the parameter...only the date. How do I do it??

 

[SQLBill]

How's this:

create procedure sp_display_activity @loginid int, @date datetime
as
SET DATEFORMAT DMY
select * from LoginActivity
where CONVERT(DATETIME,[date],101) = @date
and loginid = @loginid


EXEC sp_display_activity '1433', '12/02/2004'

By the way, I am assuming this 12/01/2004 is dd/mm/yyyy format. If not, switch the DMY to MDY. Also, if it is mm/dd/yyyy - don't forget you might not get any result since it's not Dec 2004 yet.


If that doesn't work, what errors are you getting?

-SQLBill