Home » SQL & PL/SQL » SQL & PL/SQL » passing date value to the query from sql*plus
passing date value to the query from sql*plus [message #670740] Mon, 23 July 2018 17:06 Go to next message
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 #670741 is a reply to message #670740] Mon, 23 July 2018 20:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It is poor & lazy programming to depend upon implicit datatype conversion.
To properly display a DATE datatype the conversion should be done by using TO_CHAR to convert a DATE into a properly formatted string.
The current results you observe is impacted by NLS_DATE_FORMAT in your database.

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams122.htm#REFRN10119
Re: passing date value to the query from sql*plus [message #670743 is a reply to message #670740] Tue, 24 July 2018 00:43 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous message
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>

Previous Topic: PL/SQL: ORA-04053: error occurred when validating remote object
Next Topic: How to ensure uniqueness on RAISE_APPLICATION_ERROR
Goto Forum:
  


Current Time: Thu Mar 28 16:24:11 CDT 2024