Date variable in sqlplus

Having a huge query including several temporal joins and sum over time and several date type binds. I want to execute that query using sqlplus.

SQL> variable til date
Usage: VAR[IABLE] [  [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

Using binds in sqlplus by Tanel Poder and askTom discussing missing date variable support in sqlplus. Putting those together.

SQL> variable fro varchar2(10)
SQL> exec select sysdate into :fro from dual;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where :fro < sysdate;

         1
----------
         1

SQL> print fro

FRO
--------------------------------
13.04.2011

Be aware about possible changes in execution plan caused by datatype conversions. Read the askTom discussion.

Changing NLS_DATE_FORMAT

SQL> alter session set nls_territory=america;

Session altered.

SQL> select 1 from dual where :fro < sysdate;
select 1 from dual where :fro < sysdate
                          *
ERROR at line 1:
ORA-01843: not a valid month


SQL> exec select sysdate into :fro from dual;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where :fro < sysdate;

         1
----------
         1

SQL> print fro

FRO
--------------------------------
13-APR-11

And yet another aspect about date formats

SQL> select 1 from dual where :fro = trunc(sysdate);

         1
----------
         1

SQL> alter session set nls_date_format='yyyymmdd hh24mi';

Session altered.

SQL> exec select sysdate into :fro from dual;
BEGIN select sysdate into :fro from dual; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


SQL> variable fro varchar2(13)
SQL> exec select sysdate into :fro from dual;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where :fro = trunc(sysdate);

no rows selected

SQL> print fro

FRO
--------------------------------
20110413 0902