date manipulation

norani · #1 (**permalink**) 10-21-08, 21:22

i'm preparing a stored procedure to manipulate a date datatype. Can somebody help me on how to list out all dates between 2 given dates. For example input 1 is '03/05/2008' and input 2 is '03/08/2008'.

I need the result to be :

'03/05/2008'
'03/06/2008'
'03/07/2008'
'03/08/2008'

Pls help.

stanislav.ondac · #2 (**permalink**) 10-23-08, 04:45

Code:

create function datumy (dat1 date,dat2 date)
returning set(date not null);
define diff integer;
define i integer;
define datmn set(date not null);
let diff=dat2-dat1;
let diff=diff -1;
for i in (1 to diff step 1)
insert into table(datmn) values(dat1+i);
end for

return datmn;
end function;

hope this helps

norani · #3 (**permalink**) 10-24-08, 00:08

Thank you.

But instead of inserting the result into a table can I list the result straight away. This is because I need to execute the procedure/function in the client application, I don't want to call another table to get the result.

Thanks.

stanislav.ondac · #4 (**permalink**) 10-24-08, 04:38

AFAIK this is not possible with SPL. It actually doesn't fall into philosophy of stored procedures.

norani · #5 (**permalink**) 10-24-08, 05:02

ok, thank you. i will work out on this.

Mokbol · #6 (**permalink**) 10-28-08, 06:34

stored procedure to manipulate a date datatype. Can somebody help me on how to list out all dates between 2 given dates. For example input 1 is '03/05/2008' and input 2 is '03/08/2008

Patrick Kong · #7 (**permalink**) 10-30-08, 22:24

Maybe something like this,
add code to validate from_date >= to_date;

create procedure blah (
from_date date,
to_date date)
returning date;

define return_date date;

let return_date = from_date;

while (return_date <= to_date)

return return_date;

let return_date = return_date + 1 units day;

end while;

end procedure;

Internet Services

Web Development

Digital Marketing

Consumer Tech