passing datetime param to stored procedure

[Deborah B.]

I have a stored procedure, which one of the paramenters is a datetime. I'm
using the query analyzer to test this procedure, but I cant seem to pass
a datetime value (pref the current time) without getting kicked back. I've
tried passing CURRENT_TIMESTAMP, getdate, getdate(), etc, and it doesnt like
it. In query analyzer, how in the world to i pass a datetime parameter to
the stored procedure that expects a datetime parameter?

[Bob Reed]

Try this from QA:

declare @intRV int
exec @intRV=stored_proc param1,'12/08/00 12:25:22',param3,...etc.
print @intRV
Pass the datetime as a string...works for me!
bob


"Deborah B." <computrz@hotmail.com> wrote:
>
>I have a stored procedure, which one of the paramenters is a datetime.
I'm
>using the query analyzer to test this procedure, but I cant seem to pass
>a datetime value (pref the current time) without getting kicked back. I've
>tried passing CURRENT_TIMESTAMP, getdate, getdate(), etc, and it doesnt
like
>it. In query analyzer, how in the world to i pass a datetime parameter
to
>the stored procedure that expects a datetime parameter?

[Kevin Maina]

Deborah,

I was not sure about a couple of things:
1. Are you using the variable to update a table field, calculations etc.
2. What precise error message/ error number were you getting.

Below is simple script that creates a procedure called testdate that accepts
a variable that has a datatype of 'datetime' and prints the current date
and time to screen. You could substitute the variable @mydate in your own
code.

/****************************************/
create procedure testdate
@mydate as datetime
as
select @mydate
/****************************************/

Below is the code to run the procedure 'testdate' through the Query Analyzer
so that you can pick up the current system date and time:

declare @passdate datetime
select @passdate = CURRENT_TIMESTAMP
execute testdate @passdate

I made the following assumptions in the code:
1.The default language set on the server was U.S. English.
2. Sql 7.0 or 6.5 was being used.

MORE INFORMATION

To check what default language a server has installed, use the following
SQL command:

sp_configure 'default language'

If the resulting value is 0, the default language is U.S. English. If the
result is not 0, run the following SQL command to find the installed
default language setting and date format used:

select name ,alias, dateformat
from syslanguages
where langid =
(select value from master..sysconfigures
where comment = 'default language')

SQL Server provides the ability to set the date format and other language
settings by adding another language. Just setting the regional setting in
the Windows NT Control Panel to the local region's date format will not help
in determining the date format for SQL Server.


I hope this will solve your problem.

Regards,
Kevin


Kevin Maina is Microsoft Certified in Windows NT and Microsoft SQL Server
Implementation and Design. Kevin develops application software using Microsoft
SQL Server and is a Great Plains Software Developer / Consultant.