Putting the Current Date in a Spool File Name in sqlplus

 Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;
spool &mydate.report.txt


-- my report goes here
select last_name from employees;
spool off