×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Calculate Working Days excluding Sat

Calculate Working Days excluding Sat

Calculate Working Days excluding Sat

(OP)
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
 

    #

RE: Calculate Working Days excluding Sat

(OP)
Sorry its RETURNS INTEGER; not  RETURNS DATETIME;

Still doesn't work  

RE: Calculate Working Days excluding Sat

And what's the error?  

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

RE: Calculate Working Days excluding Sat

(OP)
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')#

RE: Calculate Working Days excluding Sat

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close