Date Format and Sort in CSV Export

In the following Sql code I want the date to be in the mm-dd-yyyy format instead of the 11-FEB-09 format...
I tried using this statment "to_char(dob,'MM/DD/YYYY')"  but I keep getting errors...

There are two date formatted columns..
p.s_dt and dob...

I am also having trouble getting the export to sort_by p.s_dt...

set pages 0
set head off
set feed off
set echo off
set term off
set lines 170
column seq noprint
spool transactions.csv
select p.cmvt_cd||','||p.s_dt||','||sort_by.cust_name(p.cust_id)||','||pd.disp_name||','||pd.x_ext_price||','||pd.net||','||addr||','||city||','||
state||','||zip||','||licno||','||wt||','||dob||','||fname||','||lname||','||gender from cust_scanned_images csi, p,pd
where p.cmvt_id=pd.cmvt_id and csi.cust_id=p.cust_id and trunc(s_dt) >= trunc(sysdate) -7  and p.cmst_cd = 'PD' order by p.cmvt_cd 
/
  -- = trunc(sysdate)-7
spool off
set pages 100
exit

Open in new window

Vincent CostanzaOwnerAsked:
Who is Participating?
 
sdstuberCommented:
and with sorting change...

note, you order by the unmodified value, not the converted one.
select p.cmvt_cd||','|| to_char(p.s_dt,'mm-dd-yyyy') ||','||sort_by.cust_name(p.cust_id)||','||pd.disp_name||','||pd.x_ext_price||','||pd.net||','||addr||','||city||','||
state||','||zip||','||licno||','||wt||','|| to_char(dob,'mm-dd-yyyy')||','||fname||','||lname||','||gender from cust_scanned_images csi, p,pd
where p.cmvt_id=pd.cmvt_id and csi.cust_id=p.cust_id and trunc(s_dt) >= trunc(sysdate) -7  and p.cmst_cd = 'PD' order by p.s_pt

Open in new window

0
 
sdstuberCommented:
please do a describe on cust_scanned_images and post the output here

are your date columns actually dates?  or are they strings?
0
 
Mark GeerlingsDatabase AdministratorCommented:
As an alternative to "to_char" with a format mask (which should work) you can use this statement after the "set" statements and before the query:
alter session set nls_date_format='MM/DD/YYYY'

I think that is the correct syntax, with single quotes, but if that gives an error, try it with double quotes instead.

To test it do:
select sysdate from dual;

"I am also having trouble getting the export to sort_by p.s_dt..."
You current "order by" clause is:
order by p.cmvt_cd

Just change that to:
order by p.s_dt
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vincent CostanzaOwnerAuthor Commented:
got the sorting, that worked fine, thanks...
How do I do a Describe..? I do know the date colmns are dates, not strings...
 
Both methods I tried for date format have not worked.... Oracle dates are just weird...
I tried the Alter session line after the sets with ' and " quotes should it be after the spool??
Could you put the to_char in a section of the line so I can see the exact syntax??
Thanks again
0
 
sdstuberCommented:
from sqlplus command prompt


SQL> desc cust_scanned_images



with embedded formatting in the query it might look like the snippet below
select p.cmvt_cd||','|| to_char(p.s_dt,'mm-dd-yyyy') ||','||sort_by.cust_name(p.cust_id)||','||pd.disp_name||','||pd.x_ext_price||','||pd.net||','||addr||','||city||','||
state||','||zip||','||licno||','||wt||','|| to_char(dob,'mm-dd-yyyy')||','||fname||','||lname||','||gender from cust_scanned_images csi, p,pd
where p.cmvt_id=pd.cmvt_id and csi.cust_id=p.cust_id and trunc(s_dt) >= trunc(sysdate) -7  and p.cmst_cd = 'PD' order by p.cmvt_cd 

Open in new window

0
 
Vincent CostanzaOwnerAuthor Commented:
Thanks for everything, I have the export doing just what I want...!!!!
desc cust_scanned_images
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUST_ID                                   NOT NULL NUMBER(10)
 SCAN_ID                                   NOT NULL NUMBER(10)
 SCAN_IMG                                           LONG RAW
 LIC_TEXT                                           VARCHAR2(4000)
 LICNO                                              VARCHAR2(30)
 LICEXP                                             DATE
 FNAME                                              VARCHAR2(30)
 LNAME                                              VARCHAR2(30)
 ADDR                                               VARCHAR2(30)
 CITY                                               VARCHAR2(30)
 STATE                                              VARCHAR2(30)
 ZIP                                                VARCHAR2(10)
 DOB                                                DATE
 GENDER                                             VARCHAR2(1)
 HT                                                 VARCHAR2(5)
 WT                                                 NUMBER(4)
 ACTIVE_YN                                          VARCHAR2(1)
SQL> exit
0
 
sdstuberCommented:
glad we could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.