passing date value to the query from sql*plus [message #670740] |
Mon, 23 July 2018 17:06 |
shrinika
Messages: 306 Registered: April 2008
|
Senior Member |
|
|
Hello,
I am trying to pass the date parameter to the query
from sql*plus. It is passing the date. but not with HH:MI:SS.
SQL> var a varchar2(20);
SQL> var b varchar2(20);
SQL> exec :a:=to_date('20180723101002','yyyymmddhh24miss');
PL/SQL procedure successfully completed.
SQL> exec :b:=to_date('20180723235959','yyyymmddhh24miss');
PL/SQL procedure successfully completed.
SQL> print :a
A
--------------------------------
23-JUL-18
SQL> print :b
B
--------------------------------
23-JUL-18
SQL> select count(*) from v$active_session_history
2 where sample_time between :a and :b;
COUNT(*)
----------
0
SQL> select count(*) from v$active_session_history
2 where sample_time between
3 to_date('20180723101002','yyyymmddhh24miss')
4 and to_date('20180723235959','yyyymmddhh24miss');
COUNT(*)
----------
9712
SQL>
I can not use the date variable.
SQL> var a date;
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL>
Any help is greatly appreciated.
|
|
|
|
Re: passing date value to the query from sql*plus [message #670743 is a reply to message #670740] |
Tue, 24 July 2018 00:43 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Your problem is here,SQL> var a varchar2(20);
SQL> var b varchar2(20);
SQL> exec :a:=to_date('20180723101002','yyyymmddhh24miss'); :A is a string and you are trying to a date in it, which is impossible. So Uncle Oracle helpfully converts the date into a string first, using whatever your default date format is set to which is stripping off the time element.
You need to store the strings in your variables, then to_date them for the comparison with sample_time.
|
|
|
Re: passing date value to the query from sql*plus [message #670759 is a reply to message #670743] |
Tue, 24 July 2018 12:08 |
shrinika
Messages: 306 Registered: April 2008
|
Senior Member |
|
|
sounds good! Thanks for your input!
It worked now!
SQL> var a varchar2(20);
SQL> var b varchar2(20);
SQL> exec :a:='20180723101002'
PL/SQL procedure successfully completed.
SQL> exec :b:='20180723235959'
PL/SQL procedure successfully completed.
SQL> select count(*) from v$active_session_history
where sample_time between to_date(:a,'yyyymmddhh24miss')
and to_date(:b,'yyyymmddhh24miss'); 2 3
COUNT(*)
----------
4244
SQL>
|
|
|