pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow can i convert a substring to a date?
2+ messages / 2 participants
[nested] [flat]
* how can i convert a substring to a date?
@ 2003-01-31 07:03 joe.guyot <yusufguyot@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: joe.guyot @ 2003-01-31 07:03 UTC (permalink / raw)
To: pgsql-sql
greetings all!
i have a string data from a view that is a packed field. it
contains a date, a time and a user's initials. i'm trying to extract
the date portion in a pg 7 view. the data originally came from a ms
sql 7 table that has since been converted into a pg 7 table.
in the ms sql 7 view the date was extracted as follows:
convert(timestamp, substring(creat, 1, 8), 120) = 'createdate'
where creat is the packed field from the original table and
'createdate' is the extracted date portion. the data would typically
look like: 200111171623XYX. the result is '2001-11-17'.
i've reviewed documentation, on line books and several threads in
this and related newsgroups and can't seem to come up with a decent
solution.
i've tried various combinations of this in a pg 7 view:
to_date(substr(creat,1,8),'YYYY-MM-DD') = 'createdate'
to_date(substring(creat from 1 for 8),'YYYY-MM-DD') =
'createdate'
to_timestamp(substr(creat,1,8),'YYYY-MM-DD') = 'createdate'
to_timestamp(substrsting(creat from 1 for 8),'YYYY-MM-DD') =
'createdate'
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.
regards,
yusuf
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: how can i convert a substring to a date?
@ 2003-02-02 07:16 Ross J. Reedstrom <reedstrm@rice.edu>
parent: joe.guyot <yusufguyot@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Ross J. Reedstrom @ 2003-02-02 07:16 UTC (permalink / raw)
To: joe.guyot <yusufguyot@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-02-02 07:16 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-31 07:03 how can i convert a substring to a date? joe.guyot <yusufguyot@yahoo.com>
2003-02-02 07:16 ` Ross J. Reedstrom <reedstrm@rice.edu>
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