Improve company productivity with a Business Account.Sign Up

x
?
Solved

Having problems with UDF that uses If statements and while loop

Posted on 2004-07-30
3
Medium Priority
?
805 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Mach1pro
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
BulZeyE earned 1200 total points
ID: 11681104
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11681127
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
 
LVL 4

Expert Comment

by:BulZeyE
ID: 11681225
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question