Having problems with UDF that uses If statements and while loop

I just need some guru to fix my syntax. I spent over an hour trying to find the problem, but it just isn't obvious to me.

---------------------------------
CREATE   FUNCTION dbo.DaysOff
  (
     
      @dtStartDate smallDatetime,
      @dtEndDate smalldatetime,
      @strStartTime varchar(8),
      @strEndTime varchar(8)
   
      
  )
RETURNS decimal
AS
begin
DECLARE @dtStartTime DateTime,
      @dtEndTime DateTime,
      @sgHours decimal

If DateDiff(day,@dtStartDate , @dtEndDate) > 1
       While @dtStartDate >= @dtEndDate
            Begin
                  If DatePart(Weekday,@dtStartDate) > 1 and DatePart(Weekday,@dtStartDate) < 7                              
                  Set @sgHours = @sghours + 8            
                  set @dtStartDate = DateAdd(day,1,@dtStartDate)                  
            end
Else
      If Isdate(@strStartTime)
      
            Set @dtStartTime = Convert(datetime,@strStarttime,108)
            If IsDate(@strEndTime)
                  
                  Set @dtEndTime = Convert(DateTime,@strEndTime,108)
                  Set @sgHours = dbo.MinutesWorked(@dtstartDate, @dtStartTime, @dtEndDate, @dtEndTime) /60
                  
            Else
                  Set @sgHours = 8
      
      Else
            Set @sgHours = 8

return @sgHours

end
LVL 6
Mach1proAsked:
Who is Participating?
 
BulZeyEConnect With a Mentor Commented:
If you have more than one statement inside of an if statement you have to use BEGIN and END.  I also added an ' = 1 ' to your checks for Isdate..  Try the following:

CREATE   FUNCTION dbo.DaysOff
  (
     
     @dtStartDate smallDatetime,
     @dtEndDate smalldatetime,
     @strStartTime varchar(8),
     @strEndTime varchar(8)
   
     
  )
RETURNS decimal
AS
begin
DECLARE @dtStartTime DateTime,
     @dtEndTime DateTime,
     @sgHours decimal

If DateDiff(day,@dtStartDate , @dtEndDate) > 1
BEGIN
      While @dtStartDate >= @dtEndDate
          Begin
               If DatePart(Weekday,@dtStartDate) > 1 and DatePart(Weekday,@dtStartDate) < 7                        
               Set @sgHours = @sghours + 8          
               set @dtStartDate = DateAdd(day,1,@dtStartDate)              
          end
END
Else


     If Isdate(@strStartTime) = 1
     BEGIN
     
          Set @dtStartTime = Convert(datetime,@strStarttime,108)
          If IsDate(@strEndTime) = 1
          BEGIN
               
               Set @dtEndTime = Convert(DateTime,@strEndTime,108)
               Set @sgHours = dbo.MinutesWorked(@dtstartDate, @dtStartTime, @dtEndDate, @dtEndTime) /60
          END    
          Else
          BEGIN
               Set @sgHours = 8
          END
     END
     Else
     BEGIN
          Set @sgHours = 8
     END

return @sgHours

end


hth.
0
 
Anthony PerkinsCommented:
Also, please follow-up on your abandoned questions.  Here are all your open questions:
1 03/30/2004 500 how to use temptable as row source for c...  Open Microsoft Access
2 04/30/2004 250 Subform loses filter in adp when enterin...  Open Microsoft Access
3 05/05/2004 250 How to retain linked value in field when...  Open Microsoft Access
4 07/30/2004 300 Having problems with UDF that uses If st...  Open Microsoft SQL Server
5 07/15/2004 250 Bug crawling script  Open JavaScript

Thanks.
0
 
BulZeyECommented:
after looking at your logic, I think this would return the desired results:

CREATE FUNCTION dbo.DaysOff
(
  @dtStartDate  SMALLDATETIME,
  @dtEndDate    SMALLDATETIME,
  @strStartTime VARCHAR(8),
  @strEndTime   VARCHAR(8)
)
RETURNS DECIMAL
AS
BEGIN
  DECLARE @dtStartTime DATETIME,
          @dtEndTime   DATETIME,
          @sgHours     DECIMAL
 
  IF DATEDIFF(DAY,@dtStartDate , @dtEndDate) > 1
  BEGIN
    WHILE @dtStartDate <= @dtEndDate
    BEGIN
      IF DATEPART(WEEKDAY,@dtStartDate) > 1 AND DATEPART(WEEKDAY,@dtStartDate) < 7                        
        SET @sgHours = @sghours + 8          

      SET @dtStartDate = DATEADD(DAY,1,@dtStartDate)    
    END
  END
  ELSE
  IF ISDATE(@strStartTime) = 1 AND ISDATE(@strEndTime) = 1
  BEGIN
    SET @dtStartTime = CONVERT(DATETIME,@strStarttime,108)
    SET @dtEndTime = CONVERT(DATETIME,@strEndTime,108)
    SET @sgHours = dbo.MinutesWorked(@dtstartDate, @dtStartTime, @dtEndDate, @dtEndTime) /60
  END
  ELSE
  BEGIN
    SET @sgHours = 8
  END
 
  RETURN @sgHours

END
0
All Courses

From novice to tech pro — start learning today.