how can i convert a substring to a date?

From: "joe(dot)guyot" <yusufguyot(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: how can i convert a substring to a date?
Date: 2003-01-31 07:03:43
Message-ID: 20030131070343.62550.qmail@web20107.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Pavel Hlavnicka 2003-01-31 08:44:07 Re: calculated expressions and index use
Previous Message Dan Langille 2003-01-31 05:33:55 design review, FreshPorts change