How to generate a spool CSV file with Current Date

PROMPT
PROMPT *** my_script.sql *** START ***
PROMPT
set lines 420
set trimspool on
setpages 0
set head offset
termout offset
feedback offset
echo off
col dcol new_value mydate noprint
select to_char(sysdate,'YYYY-MM-DD') dcol from dual;
spool C:\&mydate._MY_SPOOL_FILENAME.csv

select 'INIT,'|| e.employee_id ||','|| e.department_id||','||d.manager_id||','|| d.department_id

from employees e, departments d
where e.department_id = d.department_id
order by 1
/
spool off
set echo on
set termout on
PROMPT
PROMPT *** my_script.sql *** END ***
PROMPT --Explanation:
/* The first query puts the date in the substitution variable "mydate". There is no visible output from this query because of the NOPRINT option in the COLUMN command. In the SPOOL command the first period (.) indicates the end of the variable name and is not included in the resulting string. If "mydate" contained "2013-05-14" from the first query then the spool file name would be "2013-05-14_MY_SPOOL_FILENAME.csv ". */

-- The Spool path C:\ means in 'C' drive for windows and
-- for Unix : Spool &mydate._MY_SPOOL_FILENAME.csv (file will be spooled in working directory)

Hope this clears all your Doubt.

Cheers
Rajani