tgresql.org
Sent: Thursday, January 18, 2007 12:26:03 PM
Subject: [GENERAL] Finding bogus dates
Suppose that I have a varchar column which contains dates, and some of them
are bogus, such as '1968-02-31'. Is it possible to specify a query condition
"cannot be cast to a valid date&qu
On Thu, Jan 18, 2007 at 06:05:37PM -0600, Ron Johnson wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 01/18/07 17:52, David Fetter wrote:
> > On Thu, Jan 18, 2007 at 05:42:54PM -0600, Ron Johnson wrote:
> >>
> >> On 01/18/07 17:22, Scott Ribe wrote:
> But this won't work if on
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/18/07 17:52, David Fetter wrote:
> On Thu, Jan 18, 2007 at 05:42:54PM -0600, Ron Johnson wrote:
>>
>> On 01/18/07 17:22, Scott Ribe wrote:
But this won't work if one had a text column of dates in various
formats, right?
>>> Right. In my
On 1/18/07, Ron Johnson <[EMAIL PROTECTED]> wrote:
> Right. In my case I have bad data from a source I didn't control,
exported
> via code that I do control which happens to output -MM-DD. Well,
except
> that I don't do what I need to when MM or DD are more than 2 digits, but
I'm
> going bac
On Thu, Jan 18, 2007 at 05:42:54PM -0600, Ron Johnson wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 01/18/07 17:22, Scott Ribe wrote:
> >> But this won't work if one had a text column of dates in various
> >> formats, right?
> >
> > Right. In my case I have bad data from a sourc
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/18/07 17:22, Scott Ribe wrote:
>> But this won't work if one had a text column of dates in various
>> formats, right?
>
> Right. In my case I have bad data from a source I didn't control, exported
> via code that I do control which happens to ou
> But this won't work if one had a text column of dates in various
> formats, right?
Right. In my case I have bad data from a source I didn't control, exported
via code that I do control which happens to output -MM-DD. Well, except
that I don't do what I need to when MM or DD are more than 2 d
Scott Ribe wrote:
Actually, that's the core of the direct query!
select * from foo where to_date(olddate,'-MM-DD')::text <>
olddate;
The format of the exported dates matches the default date::text format
because I specified the export that way. If not, the query would be
a little
more
> Bad dates like 02/31/2006 will be converted to sane dates.
Actually, that's the core of the direct query!
select * from foo where to_date(olddate,'-MM-DD')::text <> olddate;
The format of the exported dates matches the default date::text format
because I specified the export that way. If n
l Message-
From: Scott Ribe [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 18, 2007 3:48 PM
To: Brandon Aiken; pgsql-general postgresql.org
Subject: Re: [GENERAL] Finding bogus dates
I didn't know to_date would do that. It's better anyway. I just
continued
with the "fix and t
I didn't know to_date would do that. It's better anyway. I just continued
with the "fix and try again" approach and they're only 2 bad dates out
94,000+, so I don't have a huge problem here. I can try to do some research
and find the correct date, but failing that, the to_date approximation is
prob
tt Ribe
Sent: Thursday, January 18, 2007 1:26 PM
To: pgsql-general postgresql.org
Subject: [GENERAL] Finding bogus dates
Suppose that I have a varchar column which contains dates, and some of
them
are bogus, such as '1968-02-31'. Is it possible to specify a query
condition
"cannot be cast
am Thu, dem 18.01.2007, um 11:26:03 -0700 mailte Scott Ribe folgendes:
> Suppose that I have a varchar column which contains dates, and some of them
> are bogus, such as '1968-02-31'. Is it possible to specify a query condition
> "cannot be cast to a valid date". (It's legacy data, and I'm trying
> I suppose you could create a boolean function that does a cast, and catches
> the
> execption, returning NULL.
Yes, I was puzzling over query syntax and didn't think about a function.
That would be fine.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
> Suppose that I have a varchar column which contains dates, and some of them
> are bogus, such as '1968-02-31'. Is it possible to specify a query condition
> "cannot be cast to a valid date". (It's legacy data, and I'm trying to clean
> it up before importing into the final schema.) There probably
Suppose that I have a varchar column which contains dates, and some of them
are bogus, such as '1968-02-31'. Is it possible to specify a query condition
"cannot be cast to a valid date". (It's legacy data, and I'm trying to clean
it up before importing into the final schema.) There probably aren't
16 matches
Mail list logo
