Message-ID: <3E3D5800.50103@klaster.net>
Date: Sun, 02 Feb 2003 18:40:16 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: "joe.guyot" <yusufguyot@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how do i create a date from a substring???
References: <20030131154041.19708.qmail@web20101.mail.yahoo.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

joe.guyot wrote:
> greetings all!
> 
> [i just became a member]
> 
> 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'"
Your substring-date isn't in 'YYYY-MM-DD' format, but in 'YYYYMMDD'
Regards,
Tomasz Myrta