date [message #39083] |
Thu, 13 June 2002 15:43 |
rao
Messages: 25 Registered: July 1999
|
Junior Member |
|
|
Hi,
Iam trying to compare dates for getting hours.
gettime date;
gettime := New_time(sysdate, 'EST', 'GMT');
select count(*) from test
where
((testdate - '||''''||gettime||''''||')*24 <= 4)
testdate is date field in test table. iam using dynamic sql.
iam getting an error;
saying inconsistent datatypes error.
appreciate any help.
|
|
|
Re: date [message #39084 is a reply to message #39083] |
Thu, 13 June 2002 16:02 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
You are (incorrectly) converting the variable gettime into a string ('gettime').
Instead, use a bind variable:
execute immediate
'select count(*) from test where ((testdate - :gtime) * 24) <= 4' into v_count using gettime;
|
|
|
Re: date [message #39086 is a reply to message #39084] |
Thu, 13 June 2002 17:20 |
rao
Messages: 25 Registered: July 1999
|
Junior Member |
|
|
todd thanks
iam using this select in an inline query
can i do execute immediate in an inline query.
gettime date;
begin
gettime := New_time(sysdate, 'EST', 'GMT');
open c1 for
'select testname, testid from
test,
(select count(*) from test where
(testdate - gettime)*24 <= 4);
can use
execute immediate
'select count(*) from test where
(testdate - :gettime)*24 <=4;
in the above inline query
appreciate any help
|
|
|
Re: date [message #39093 is a reply to message #39084] |
Fri, 14 June 2002 08:30 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
I'm not sure where you're trying to go with this query. You have not aliased the inline view or its column (count(*)), and so its value isn't reflected in your select statement.
To answer your question though, your entire query would have to be NDS, not just the inline view part.
|
|
|