Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Date Format and Sort in CSV Export

Posted on 2009-02-18
7
Medium Priority
?
814 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Vincent Costanza
  • 4
  • 2
7 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1400 total points
ID: 23677250
please do a describe on cust_scanned_images and post the output here

are your date columns actually dates?  or are they strings?
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 600 total points
ID: 23677275
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
 

Author Comment

by:Vincent Costanza
ID: 23677739
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1400 total points
ID: 23678190
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1400 total points
ID: 23678194
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
 

Author Comment

by:Vincent Costanza
ID: 23678599
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 23680403
glad we could help
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question