[newbie] function question

[Riyaz Mansoor]

SQL Server 2005. The function below compiles fine. If the two dates
are same return 1 (true) else false.

But the testing gives an type error

execute issameday getdate, getdate

error -> Error converting data type nvarchar to datetime.
Works for

execute issameday '08/08/08', '08/08/08'

I don't understand what is going on help please


create function IsSameDay (
@sampledate datetime,
@checkdate datetime = getdate
)
returns bit
as
begin

declare @days int
set @days = datediff(day, @sampledate, @checkdate)

if @days = 0 return 1
return 0

end
go

 

[Wolfgang Müller]

Hallo,

you can try this

Select dbo.issameday(getdate(),getdate());

The error you received is caused by the incorrect syntax of the
EXECUTE-statement.

Wolfgang

 

[Sylvain Lafontaine]

Your problem here is that you cannot use a default value such as getdate()
for a function because all User Defined Functions (UDF) must be
deterministic. The following is legal:

create function IsSameDay (@sampledate datetime, @checkdate datetime =
'2008/08/08') ....

but the following is not:


create function IsSameDay (@sampledate datetime, @checkdate datetime =
getdate()) ....

When your write « create function IsSameDay (@sampledate datetime,
@checkdate datetime = getdate) .... » and try to use it; SQL-Server tries to
convert the string value « getdate » to a date, hence the error message. If
you want to use the getdate() function, you must use it when calling the
UDF:

select dbo.issameday ('08/08/08', getdate())