Every time i run this function getting an error not sure what i am doing wrong.

Using Pervasive 9.71

CREATE FUNCTION WorkDays
        (IN :StartDate DATETIME, IN :EndDate DATETIME)
     RETURNS DATETIME;
 
        BEGIN
            RETURN     
               (SELECT
                 (DATEDIFF(day,:StartDate,:EndDate)+1)
                -(DATEDIFF(week,:StartDate,:EndDate)*1)
               -(CASE WHEN DATENAME(weekday,:StartDate) = 'Sunday'
                      THEN 1
                      ELSE 0
                  END)
               -(CASE WHEN DATENAME(weekday,:EndDate) = 'Sunday'
                      THEN 1
                      ELSE 0
                  END)
                  
                -(CASE WHEN DATEDIFF(day, :StartDate, :EndDate)< 7
                        THEN 1
                        ELSE 0
                   END));
                
    END
 

    #

Sorry its RETURNS INTEGER; not  RETURNS DATETIME;

Still doesn't work  

And what's the error?  

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Hi

Forgot to mention the function can be created but when i try to use the function in the select statement get this message pop-up [LNA][Pervasive][ODBC Engine Interface]Expression evaluation error.

Statement  
select WorkDays('2009-01-01 00:00:00','2009-01-09 00:00:00')#

Resolved my issue by removing select case   

CREATE FUNCTION WorkDays
        (IN :StartDate DATETIME, :EndDate DATETIME )
     RETURNS INTEGER;
 
 
        BEGIN
          DECLARE :Var1 Integer;
          DECLARE :Var2  Integer;       
          DECLARE :Var3  Integer;
          
          if DATENAME(weekday,:StartDate) = 'Sunday' then
          set :Var1 = 1;
          else set :Var1 = 0;
          end if;
          
          if DATENAME(weekday,:EndDate) = 'Sunday' then
          set :Var2 = 1;
          else set :Var2 = 0;
          end if;
 
          if DATEDIFF(day, :StartDate, :EndDate)< 7  then
          set :Var3 = 1;
          else set :Var3 = 0;
          end if;         
         
          
            RETURN     
               (
                (DATEDIFF(day,:StartDate,:EndDate)+1)
                 -(DATEDIFF(week,:StartDate,:EndDate)*1)
                 - :Var1
                 - :Var2
                 - :Var3
                                                 
                );
                
    END