UDF - If then else syntax

[rmatthew]

Nothing like try to script/code in something that you haven't ever done prior to put you in your place :(

 

I am having trouble getting a if then else structure in a UDF -

 

CREATE function aw_GetShiftDate (@excavator varchar(20),@datetime datetime )

returns datetime

as

begin

Declare @return datetime

Set @return = convert(datetime,Convert(varchar,DatePart(month,@datetime)) + '-' + Convert(varchar,DatePart(day,@datetime))+ '-' + Convert(varchar,DatePart(year,@datetime)) ,110)
Set @datetime = Convert(datetime,Convert(varchar,DatePart(hour,@datetime)) + ':'+ Convert(varchar,datepart(minute,@datetime)) + ':' + Convert(varchar,datepart(second,@datetime)),8)

Declare @id numeric(5,0)
Declare @order numeric(5,0)
Declare @shft_start varchar(20)
Declare @shft_end varchar (20)

declare crs_cnst cursor local static for
SELECT     item_id,shft_ord,shft_strttm, shft_endtm
FROM         shifts
WHERE     (excav_id = @excavator OR
excav_id LIKE '%|' + @excavator + '|%')  order by shft_strttm
for read only

open crs_cnst
fetch crs_cnst into @id,@order,@shft_start,@shft_end

while @@fetch_status >= 0 

begin

if datediff(second,@shft_start,@shft_end) < 0 and (@datetime >= @shft_start and @datetime <= Convert(datetime,'23:59:00')) or (@datetime >= convert(datetime,'00:00:00') and @datetime <= @shft_end)

if dbo.aw_shiftsonend(@excavator) = 'True' and @datetime >=  @shft_start and @datetime <= Convert(datetime,'23:59:00')
	Set @return = Dateadd(day,1,@return)
else
	if @datetime >= convert(datetime,'00:00:00') and @datetime <= @shft_end
	Set @return = DateAdd(day,-1,@return)


fetch crs_cnst into @id,@order,@shft_start,@shft_end

end

deallocate crs_cnst

return @return

end

 

 

 

Set @return = DateAdd(day,-1,@return)

executes as an else to the first if instead of being used with the second if

[PlausiblyDamp]

Try changing the if block to

if dbo.aw_shiftsonend(@excavator) = 'True' and @datetime >=  @shft_start and @datetime <= Convert(datetime,'23:59:00')
Set @return = Dateadd(day,1,@return)
else
begin
if @datetime >= convert(datetime,'00:00:00') and @datetime <= @shft_end
Set @return = DateAdd(day,-1,@return)
end