Re: how can i convert a substring to a date?

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"
FROMbaz;
 
CREATE VIEW
test=# select * from baz;     creat
-----------------200111171623XYX
(1 row)
test=# select * from quux ;createdate | User
------------+------2001-11-17 | XYX
(1 row)
Ross