Add Date and Time Stamp File Name 1

[iamtrying]

Hi I am using Oracle 9i and Oracle's job scheduler to export data at specific intervals. I would like to add the date and timestamp to the file name for each file. I have been successful adding the date within the export, but not to the name. Here is what I have at this time. Thank you

set pagesize 0;
set timing off;
set heading off;
set trimspool on;
set feedback off;
alter session set nls_date_format='MMDDYYYY';
select to_char(sysdate,'MM-DD-YYYY') sdate from dual;
select TO_CHAR(sysdate) from dual;
spool G:\KNX_Export\KNX_SCHEDULED_SHIFTS.txt

select
(e."personnelID")||','||
Trim(To_Char(dp."opDate",'MM/DD/YYYY'))||','||
Trim(dbo.hh_mma(dp."actDrivBegTime"))||','||
Trim(dbo.hh_mma(dp."actDrivEndTime"))||','||
Trim(dp."dailyPieceFlags")||','||
Trim(dp."blockID")||','||
Trim(dp."boardID")||','||
Trim(dp."runNumber")
from "dailyPiece" dp, "employeeStatus" es, "employee" e
where dp."opDate" between SYSDATE -3 AND SYSDATE and
es."emp_SID" = dp."emp_SID" and es."dateEffective" <= dp."opDate" and es."dateEnd" >= dp."opDate"
and e."emp_SID" = dp."emp_SID"
order by e."personnelID", dp."opDate", dp."actDrivBegTime";
spool off;

 

[LKBrwnDBA]

Try this:

-- etc --
col  timstmp new_value timstmp
break on report
select to_char(sysdate,'YYYY-MM-DD-HH24-MI') timstmp from dual;
spo G:\KNX_Export\KNX_SCHEDULED_SHIFTS_&&timstmp..txt
-- etc --

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[iamtrying]

Thank you LKBrwnDBA, you made it look so easy. It's exactly what I needed.

 

[SantaMufasa]

IAmTrying, Please click on LKBrownDBA's "

Like this post? Star it!" to let him know how much you appreciate his "exactly what I needed" help.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.