Date within spooled text file

[cd972]

I am having trouble with a date field withing my spool file. In the table I am creating the text file from, the field is a date field. I cannot seem to retain that when I am creating the spool file. It converts the date to 03-NOV-08. Is there a way to correct this? Here is my spool script. The field I am having trouble with is t.unlist_date. I have tried a few variations (such as to_date, simply putting in SYSDATE) and nothing works.

SET trimspool ON
SET linesize 32767
SET wrap off
SET termout off
SET feedback off
SET Heading off
SET pagesize 0
SET trim ON
SPOOL c:\&mmddyy..txt
SELECT
'0000000141'||LPAD(to_char(rownum), 10, '0')||'00000000000000000000'||t.id_number||
decode(t.new_mod,'N','A','M')||chr(9)||
t.id_number||chr(9)||
e.person_or_org||chr(9)||
' '||chr(9)||
trim(decode(new_mod,'N',0,t.xsequence))||chr(9)||
t.type_code||chr(9)||
t.status_code||chr(9)||
NVL(t.zipcode,' ')||chr(9)||
' '||chr(9)||
t.pref_ind||chr(9)||
' '||chr(9)||
NVL(t.street1,' ')||chr(9)||
NVL(t.street2,' ')||chr(9)||
NVL(t.street3,' ')||chr(9)||
NVL(t.street4,' ')||chr(9)||
NVL(t.city,' ')||chr(9)||
NVL(t.state,' ')||chr(9)||
NVL(t.county,' ')||chr(9)||
NVL(t.country,' ')||chr(9)||
NVL(t.area_code,' ')||chr(9)||
NVL(t.xnumber,' ')||chr(9)||
NVL(t.extension,' ')||chr(9)||
NVL(t.unlist_ind,' ')||chr(9)||
t.unlist_date||chr(9)||
NVL(t.fax_area_code,' ')||chr(9)||
NVL(t.fax_number,' ')||chr(9)||
'N'||chr(9)||
-- null||chr(9)||
0||chr(9)||
null||chr(9)||
null||chr(9)||
'00000000'||chr(9)||
-- NVL(to_number(substr(t.employment_link,9,3)),0)||chr(9)||
NVL(t.employ_sequence,0)||chr(9)||
' '||chr(9)||
' '||chr(9)||
decode(nvl(t.change_source_code,' '),' ','SCH',t.change_source_code)||chr(9)||
NVL(t.xcomment,' ')||chr(9)||
null||chr(9)||
null||chr(9)||
null||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
NVL(t.care_of,' ')||chr(9)||
NVL(t.company_title,' ')||chr(9)||
NVL(t.company_name_1,' ')||chr(9)||
NVL(t.company_name_2,' ')||chr(9)||
NVL(t.email_address,' ')||chr(9)||
' '||chr(9)||
decode(NVL(t.xnumber,' '),' ','A')||chr(9)||
null||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
NVL(t.emp_title,' ')||chr(9)||
NVL(t.foreign_phone,' ')||chr(9)||
NVL(t.foreign_fax,' ')||chr(9)||
NVL(t.zip_suffix,' ')||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
NVL(t.business_ind,' ')||chr(9)||
decode(t.type_code,'B','*',' ')||chr(9)||
' '||chr(9)||
' '||chr(9)||
null||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '||chr(9)||
' '
from entity e,
sysstp.address_mod t
where t.updated = 'Y'
and t.id_number = e.id_number;

SPOOL off
SET trimspool off
SET linesize 132
SET wrap off

 

[SantaMufasa]

CD,

It would be helpful for us in helping you, if we knew what you wanted the outgoing format of the DATE to look like.

I have tried a few variations such as to_date...

If you want your outgoing DATE to appear a particular way, then you use the TO_CHAR function which transforms a DATE into a differently appearing character string.


Again, if you let us know what you want the outgoing DATE to look like, then we can assist you in creating the appropriate TO_CHAR function.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cd972]

I want the date to appear exactly as it is in the table. It is a date field.
11/3/2008 9:52:30 AM

Thank you.

 

[SantaMufasa]

I want the date to appear exactly as it is in the table. It is a date field. 11/3/2008 9:52:30 AM

First, if a field is of type DATE in Oracle, then it has no specific appearance...a DATE field contains only DATE information, which is always the following:[ul][li]Meridian-of-Time indicator (negative = B.C.; positive = A.D.)[/li][li]Century (with limits of +/- 99)[/li][li]Year (with limits of 0 - 99)[/li][li]Month (with limits of 1 - 12)[/li][li]Day (with appropriate limits for each month)[/li][li]Hour (with limits of 0 - 23)[/li][li]Minute (with limits of 0 - 59)[/li][li]Second (with limits of 0 - 59)[/li][/ul](If you want sub-second granularity, Oracle provides other data-type options.)



If you want a truly DATE datatype expression to appear as you have formatted it above, then you can use the TO_CHAR function on that expression as I do here:

select to_char(sysdate,'fmmm/dd/yyyy hh:mi:ss PM') Now from dual;

NOW
----------------------
11/3/2008 9:10:49 AM

Let us know if this resolves your question/need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[sem]

alter session set nls_date_fromat='mm/dd/yyyy hh:mi:ss PM'

Regards, Dima

http://seminihin.narod.ru