[newbie] function question

  • Thread starter Thread starter Riyaz Mansoor
  • Start date Start date
R

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
Click to expand...
error -> Error converting data type nvarchar to datetime.
Works for
execute issameday '08/08/08', '08/08/08'
Click to expand...

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
 
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
 
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())
 
Back
Top