export and dateformat to produce CSV file

[hikums]

Hello,

If I export a file containing date field,
export to "/james/j.txt" of DEL
select a1,adate from a

The date is represented in YYYY/MM/DD format as expected.

Output I need is to be in "yyyymmdd" without delimiter. MODIFIED BY
DATESISO option has a delimiter.

I am getting the same ouput despite using the following:
export to "/james/j.txt" of DEL
select a1,replace(char (adate),'/','') from a

From using just the select statement and running from command line with

-z option, I am able to redirect output to a file, but it is not in CSV
format. This is a big SQL and I do not want to manually insert commas
to separate in select statement.

Can someone throw light on this. Specifically for the export with
modified by, and for using select in command line and get a CSV format.
Thanks a bunch.

[Matt Emmerton]

<hi****@gmail.c om> wrote in message
news:11******** *************@c 13g2000cwb.goog legroups.com...

Hello,

If I export a file containing date field,
export to "/james/j.txt" of DEL
select a1,adate from a

The date is represented in YYYY/MM/DD format as expected.

Output I need is to be in "yyyymmdd" without delimiter. MODIFIED BY
DATESISO option has a delimiter.

I am getting the same ouput despite using the following:
export to "/james/j.txt" of DEL
select a1,replace(char (adate),'/','') from a

From using just the select statement and running from command line with

-z option, I am able to redirect output to a file, but it is not in CSV
format. This is a big SQL and I do not want to manually insert commas
to separate in select statement.

Can someone throw light on this. Specifically for the export with
modified by, and for using select in command line and get a CSV format.
Thanks a bunch.

The DB2 documentation sheds some light on this - specifically footnote 3:

http://publib.boulder.ibm.com/infoce...e/r0011046.htm

If you're using DB2 v8 FP2 or later, the following command will do what you
want:

EXPORT TO "/james/j.txt" OF DEL
MODIFIED BY TIMESTAMPFORMAT ="YYYYMMDD"
SELECT a1, adate FROM a

If you have Perl, you can easily remove the delimiters using the following
command:

perl -pi -e 's#/##g' /james/j.txt

--
Matt Emmerton

[hikums]

EXPORT TO "/james/j.txt" OF DEL
MODIFIED BY nochardel
SELECT a1, adate FROM a

The above works fine:
I/p data:
c1 bdate
------------------
1 2005-01-28

2 2005-01-28

21 2005-01-28

22 2005-01-28

41 2005-01-28

42 2005-01-28
Output in csv format picked up in spreadsheet:

1 20050128
2 20050128
21 20050128
22 20050128
41 20050128
42 20050128
Matt Emmerton wrote:

<hi****@gmail.c om> wrote in message
news:11******** *************@c 13g2000cwb.goog legroups.com...

Hello,

If I export a file containing date field,
export to "/james/j.txt" of DEL
select a1,adate from a

The date is represented in YYYY/MM/DD format as expected.

Output I need is to be in "yyyymmdd" without delimiter. MODIFIED BY
DATESISO option has a delimiter.

I am getting the same ouput despite using the following:
export to "/james/j.txt" of DEL
select a1,replace(char (adate),'/','') from a

From using just the select statement and running from command line

with -z option, I am able to redirect output to a file, but it is not in CSV format. This is a big SQL and I do not want to manually insert commas to separate in select statement.

Can someone throw light on this. Specifically for the export with
modified by, and for using select in command line and get a CSV format. Thanks a bunch.
The DB2 documentation sheds some light on this - specifically

footnote 3:
http://publib.boulder.ibm.com/infoce...e/r0011046.htm
If you're using DB2 v8 FP2 or later, the following command will do what you want:

EXPORT TO "/james/j.txt" OF DEL
MODIFIED BY TIMESTAMPFORMAT ="YYYYMMDD"
SELECT a1, adate FROM a

If you have Perl, you can easily remove the delimiters using the following command:

perl -pi -e 's#/##g' /james/j.txt

--
Matt Emmerton