From: Tomasz Myrta <jasiek@klaster.net>
To: joe.guyot <yusufguyot@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how do i create a date from a substring???
Date: Sun, 02 Feb 2003 18:40:16 +0100
Message-ID: <3E3D5800.50103@klaster.net> (raw)
References: <20030131154041.19708.qmail@web20101.mail.yahoo.com>
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
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: jasiek@klaster.net, yusufguyot@yahoo.com
Subject: Re: how do i create a date from a substring???
In-Reply-To: <3E3D5800.50103@klaster.net>
* 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