User defined Function datetime parameter bug ??

  • Thread starter Thread starter Xdudez
  • Start date Start date
X

Xdudez

I recently came accross a vary strange situation. I created a UDF in
SQL Server 200 as:

CREATE FUNCTION dbo.CheckWeekend
(@sDate datetime)
RETURNS bit

AS
BEGIN
Declare @DayNum tinyint
Declare @ret bit

set @DayNum=datepart(dw,@sDate)

if ((@DayNum=1) or (@DayNum=7 ))
set @ret=1
else
set @ret=0

RETURN @ret

END

This function returns -1 if @sDate is Saturday or Sunday and 0 for the
other days. I tried this from within SQL Query Analyser and it's OK.

When i test this UDF From Visual Studio 2003 Server Explorer->Sql
Server->...-> funtions, IT DOES NOT WORK !!

I tried Stepping in the function and realised that @sDate is 2/1/1900
00:00:00, for whatever my input date might be !!

I also tried to access this UDF from vb.net through ado.net, and
although no exception is generated the result is 0 for any date as
input.

I finally tried to execute the sub i had created from 'Command Window
- Imediate'

?CheckWeekDay(New DateTime(2004, 3, 20))

and only then i got an exception that might help:
"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and
12/31/9999 11:59:59 PM."

I also created a stored procedure that called that UDF, and called
this sp from vb.net, the result was the same, it did not work,
although it did work perfectly from within Query Analyser !

Any help would be greatly appreciated
 
I would try to use SQL Profiler to see what is passed to the function( don't
you pass it as text ?)

Patrice
 
Back
Top