September 30, 2008 at 9:35 pm
I have a sproc with a datetime parameter default, as:
@Startdate datetime = getdate
I'm expecting when I pass nothing to have the default applied, but instead I get a conversion error 'converting datetime from character string'.
Even when I call the sproc using 'GetDate' as the parameter, it still gives the same error.
Can somone enlighten me as to why this does not work.
Thanks
Peter
September 30, 2008 at 9:51 pm
You can't use the fuction getdate() as a default. You need to do something like this:
create proc dbo.MyTest (
@StartDate datetime = null
)
as
begin
set @StartDate = isnull(@StartDate,getdate());
select @StartDate;
return (0)
end;
go
exec dbo.MyTest;
go
😎
September 30, 2008 at 10:02 pm
Thanks Lynn:)
I got this working but was not sure of the reason. Is it also the case that I can't getdate() directly as an input parameter, as in:
exec procname getdate()
September 30, 2008 at 10:29 pm
Added an additional test:
create proc dbo.MyTest (
@StartDate datetime = null
)
as
begin
set @StartDate = isnull(@StartDate,getdate());
select @StartDate;
return (0)
end;
go
exec dbo.MyTest;
go
exec dbo.MyTest getdate(); -- this one fails with incorrect syntax near )
go
declare @TestDate datetime;
set @TestDate = getdate()
exec dbo.MyTest @TestDate;
go
drop proc dbo.MyTest;
go
Yes, you can't use a function call directly when executing a stored procedure (at least in this case).
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply