asked on

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

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vincent Costanza

ASKER

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

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vincent Costanza

ASKER

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

Sean Stuber

glad we could help