Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculate Working Days excluding Sat

Status
Not open for further replies.

caddy1

Technical User
Aug 28, 2009
4
GB
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top