By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

date between problem

100+
P: 138
hi all,
i have created a function. i got the problem. it doesn't show the out put if i put between condition. but it works perfectly when used only ">" condition only in the date
ie
create or repl...
...
..
to_char('||quote_ident(date_type)||',''yyyy-mm-dd'')
> '||from_date ;

....
....
....

the function is

CREATE OR REPLACE FUNCTION fnGetUser(date, date, text )
RETURNS SETOF user_report AS
$BODY$
declare
from_date alias for $1;
to_date alias for $2;
date_type alias for $3;

query refcursor;
r user_report%rowtype;
begin
open query for execute 'SELECT
id_no,
entry_dt,
name,
tel
from tbl_user
where to_char('||quote_ident(date_type)||',''yyyy-mm-dd'')
between '||from_date||' and ''||to_date;
loop
fetch query into r;
if not found then return ;
else
return next r;
end if;
end loop;
close query;
return;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

also when i used "<"(lesser than) condition, it doesn't show the result as well.
so i want to know how to use the "between" condition to check the date.

TIA
Feb 10 '10 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 700
What is the value of datestyle setting?
Expand|Select|Wrap|Line Numbers
  1. show datestyle;
  2.  
what are the values in the table in columns from_date i to_date?
Feb 21 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.