Re: SQL PLUS question

Jonathan Leffler wrote: > > >From: chalu kim <chalu@ivillage.com> > >Date: Mon, 27 Jan 1997 12:56:08 -0800 > >X-Informix-List-Id: <news.33057> > > > >We are moving data from ORACLE to Informix; we are having a problem with > >SQLPLUS while trying to dump the data to file. How can we control the > >way SQLPLUS outputs a DATE type field. Currently when running a select > >statement to output a DATE type field, it outputs in form of > >DAY-MONTH-YEAR, EX: 27-JAN-97. Is there some way to have a DATE field > >output in a format that INFORMIX dbload command could work with? > >EX: MONTH-DAY-YEAR 01-27-97. > > If you set DBDATE=dmy2-, then dbload should accept what SQLPLUS emits. > > For example: > > $ dbaccess apt - <<! > > CREATE TABLE DateField (Col00 DATE NOT NULL);> > ! > > Database selected. > > Table created. > > Database closed. > > $ cat dbload.data > 27-JAN-97| > $ cat dbload.cmd> file "dbload.data" delimiter "|" 1; > insert into datefield;> $ dbload -d apt -c dbload.cmd -l dbload.log> > DBLOAD Load Utility INFORMIX-SQL Version 7.21.UC1 > Copyright (C) Informix Software, Inc., 1984-1996 > Software Serial Number JLS#J961022 > > Table datefield had 1 row(s) loaded into it > $ echo $DBDATE > dmy4/ > $> > Yours, > Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> You can SELECT TO_CHAR(DATE_COLUMN_NAME, 'Month DD,YYYY') which would output January 28, 1997. There are loads of format models (see the Server SQL Reference). If you want more info, let me know. Regards....Richard -- \\|||/ (. .) ooO-(_)-Ooo-------------------------------------------- Name: Richard Fisher | Specialist MSE E-mail Address | Richard_Fisher@amdahl.com =========================================================== Any opinions expressed above are mine and do not necessarily represent the opinions or policies of Amdahl Corporation =========================================================== My opinions may have changed, but not the fact that I'm right! ___________________________________________________________