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?
Date: Sun, 2 Feb 2003 01:16:03 -0600
Message-ID: <20030202071603.GA2224@wallace.ece.rice.edu> (raw)
In-Reply-To: <20030131070343.62550.qmail@web20107.mail.yahoo.com>
References: <20030131070343.62550.qmail@web20107.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu, yusufguyot@yahoo.com
Subject: Re: how can i convert a substring to a date?
In-Reply-To: <20030202071603.GA2224@wallace.ece.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox