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
#
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
#
Talk To Other Members
RE: Calculate Working Days excluding Sat
Still doesn't work
RE: Calculate Working Days excluding Sat
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: Calculate Working Days excluding Sat
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
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