Date: Sun, 2 Feb 2003 01:16:03 -0600
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: "joe.guyot" <yusufguyot@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how can i convert a substring to a date?
Message-ID: <20030202071603.GA2224@wallace.ece.rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	"joe.guyot" <yusufguyot@yahoo.com>, pgsql-sql@postgresql.org
References: <20030131070343.62550.qmail@web20107.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20030131070343.62550.qmail@web20107.mail.yahoo.com>
User-Agent: Mutt/1.4i

On Thu, Jan 30, 2003 at 11:03:43PM -0800, joe.guyot wrote:
> greetings all!
> 
> 
> and continually get different errors:
>     "bad date external representation 'createdate'"
> or
>     "bad timestamp external representation 'createdate'"
> 
> i'm sure this has an obvious solution but i can't seem to find it.
> any suggestions are appreciated.

Hmm, the parse is telling you it doesn't know how to express the string
'createdate' as a date or timestamp. Why is that? Because you've 
asked it to. I presume the fragments you quote above are part of a 
CREATE VIEW statement. You're asking forthe boolean result of comparing
the substring expression to the string 'createdate'. What you probably
want is:

to_date(substr(creat,1,8),'YYYYMMDD') AS 'createdate'

Here's an example of use:

test=# CREATE VIEW quux AS SELECT to_date(substr(creat,1,8),'YYYYMMDD') AS "createdate", substr(creat,13) AS "User" FROM baz;
CREATE VIEW
test=# select * from baz;
      creat
-----------------
 200111171623XYX
(1 row)
 
test=# select * from quux ;
 createdate | User
------------+------
 2001-11-17 | XYX
(1 row)
 
Ross