=?iso-8859-1?q?DBDATE_/_DBMONEY_in_SQL_=E4ndern_=3F=3F?=

Posted in 2007

Summary

A German-language post asked how to load data whose dates are formatted as 2007-07-10 using the SQL LOAD statement (not dbload), since changing DBDATE applies to the whole session/environment. Carsten Haese translated the question and said he knew of no SQL command to set the date format; his workaround was to load into a staging table with char(10) columns and then convert with mdy(x[6,7],x[9,10],x[1,4]) when copying to the target. Fernando Nunes suggested simply setting DBDATE before the load, or piping the file through awk to reformat, but asked for the real constraints. The thread then drifted off-topic and no confirmation from the original poster is recorded.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

hoffiman@googlemail.com — Tue, 10 Jul 2007 09:39:06 -0000 — source: Usenet: comp.databases.informix

Hallo zusammen, ich möchte ein Daten in die Datenbank landen - in den Daten ist das Datum mit 2007-07-10 angegeben. Ich möchte die Daten aber nicht mit dbload sondern mit dem Befehl load laden. ist es möglich das Datumsformat unter SQL zu ändern. Wenn ich die Enviorment Variable DBDATE ändere dann gilt das gleich für die ganze Season und ich muss unter Unix laden. Danke und Gruß Matthias

↪ replying to hoffiman@googlemail.com

Carsten Haese — Tue, 10 Jul 2007 08:51:46 -0400 — source: Usenet: comp.databases.informix

On Tue, 2007-07-10 at 09:39 +0000, hoffiman@googlemail.com wrote: > Hallo zusammen, > > ich möchte ein Daten in die Datenbank landen - in den Daten ist das > Datum mit 2007-07-10 angegeben. > > Ich möchte die Daten aber nicht mit dbload sondern mit dem Befehl load > laden. > > ist es möglich das Datumsformat unter SQL zu ändern. > > Wenn ich die Enviorment Variable DBDATE ändere dann gilt das gleich > für die ganze Season und ich muss unter Unix laden. > > Danke und Gruß > > Matthias [Note that this list is usually in English. Your chances of reaching a knowledgeable person increase dramatically if you restate your question in English.] Mir ist keine Möglichkeit bekannt, das Datumsformat durch einen SQL-Befehl zu verändern. Eine Möglichkeit wäre es, die Daten zuerst in eine Hilfstabelle zu laden. Die Hilfstabelle würde identisch zur Zieltabelle aussehen, außer das Date-spalten durch Char(10)-spalten ersetzt sind. Danach kann man dann die Daten in die Zieltabelle übertragen und die Datumsspalten mit mdy(x[6,7],x[9,10],x[1,4]) in richtige Daten übersetzen. In English: I'm not aware of a way to set the date format with an SQL command. One possibility would be to load the data into an auxiliary table that is identical to the target table except that date columns are replaced by char(10) columns. You can then transfer the data to the target table and translate the date columns with mdy(x[6,7],x[9,10],x[1,4]) into real dates. Hope this helps, -- Carsten Haese http://informixdb.sourceforge.net

↪ replying to hoffiman@googlemail.com

Carsten Haese — Tue, 10 Jul 2007 09:34:22 -0400 — source: Usenet: comp.databases.informix

↪ replying to Carsten Haese

Fernando Nunes — Tue, 10 Jul 2007 15:24:54 +0100 — source: Usenet: comp.databases.informix

Carsten Haese wrote: > On Tue, 2007-07-10 at 09:39 +0000, hoffiman@googlemail.com wrote: >> Hallo zusammen, >> >> ich m'chte ein Daten in die Datenbank landen - in den Daten ist das >> Datum mit 2007-07-10 angegeben. >> >> Ich m'chte die Daten aber nicht mit dbload sondern mit dem Befehl load >> laden. >> >> ist es m'glich das Datumsformat unter SQL zu 'ndern. >> >> Wenn ich die Enviorment Variable DBDATE 'ndere dann gilt das gleich >> f'r die ganze Season und ich muss unter Unix laden. >> >> Danke und Gru' >> >> Matthias > > To eliminate confusion and to solicit better advice from the resident > experts, here is my translation of Matthias' question: > > <<<< > I want to load data into a database. In the data, dates are given as > 2007-07-10. > > However, I don't want to load the data with dbload, but with the load > command instead. > > Is it possible to change the date format under SQL? > > When I set the DBDATE environment variable, it is set for the entire > season (session?) and I have to load under Unix. > > Now you know as much as I do. > That was more or less what automatic translation told me, but I couldn't see the exact problem... Let's see... to use load he is probably working with dbaccess/isql. In that what is the problem of setting DBDATE, loading and then do whatever he wants? Does he need it to be within a single transaction? He could of course make the file a pipe, and use AWK to translate the data into a proper format and pass it through the pipe... there are several solutions, but first we would need to understand the original constraints, which I didn't. Regards, -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

↪ replying to Carsten Haese

Captain Pedantic — Tue, 10 Jul 2007 16:20:54 +0100 — source: Usenet: comp.databases.informix

"Carsten Haese" <carsten@uniqsys.com> wrote in message news:mailman.394.1184071906.13675.informix-list@iiug.org... [Note that this list is usually in English. Your chances of reaching a knowledgeable person increase dramatically if you restate your question in English.] Your chances of reaching a bitter, sad misanthrope hiding behind a pseudonym are also increased greatly!

↪ replying to Captain Pedantic

Obnoxio The Clown — Tue, 10 Jul 2007 19:31:35 +0100 (BST) — source: Usenet: comp.databases.informix

Captain Pedantic said: > "Carsten Haese" <carsten@uniqsys.com> wrote in message > news:mailman.394.1184071906.13675.informix-list@iiug.org... > > [Note that this list is usually in English. Your chances of reaching a > knowledgeable person increase dramatically if you restate your question > in English.] > > Your chances of reaching a bitter, sad misanthrope hiding behind a > pseudonym > are also increased greatly! You called? -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.