Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by hub.org (8.9.2/8.9.1) with ESMTP id LAA04906 for ; Wed, 31 Mar 1999 11:01:23 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: from rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (8.9.0/8.9.0) with ESMTP id KAA04093 for ; Wed, 31 Mar 1999 10:01:20 -0600 (CST) Sender: reedstrm@ece.rice.edu Message-ID: <370246D0.4F395EE7@rice.edu> Date: Wed, 31 Mar 1999 10:01:20 -0600 From: "Ross J. Reedstrom" Organization: NSBRI X-Mailer: Mozilla 4.06 [en] (X11; U; Linux 2.2.3-ac3 i686) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: [SQL] merging date and time References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit miannaco@csc.com wrote: > > Hi, > I'm experiencing some problems to create a view in which I put together a > date and a time in the same field... > > ERROR: Bad datetime external representation 'Mon 22 Mar 00:00:00 1999 > MET14:45:27' > > does anyone have an idea about how to solve this? Well, the error is exactly what it says - that's not a date time. AS you can see, the text version of the 'date' type includes a time of 00:00:00. This lead us to a clue - you need to add the time to the date before the conversion: test=> create table t1 (d date, t text); CREATE test=> insert into t1 values('22-03-1999','14:45:27'); INSERT 101293 1 test=> select datetime(d+timespan(t)) from t1; datetime ---------------------------- Mon Mar 22 14:45:27 1999 CST (1 row) test=> drop table t1; DROP test=> HTH, Ross P.S. no need to send your question twice - and it appeared three times! -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005