Skip site navigation (1) Skip section navigation (2)

Re: issue with an assembled date field

From: Chris Bowlby <excalibur(at)accesswave(dot)ca>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: issue with an assembled date field
Date: 2008-02-29 18:28:23
Message-ID: 1204309703.2839.46.camel@efnisien.dreadnet.org (view raw or flat)
Thread:
Lists: pgsql-general
Hi Colin, 

 Thanks for your response, if I remove the where clause from my example,
I also am able to execute the query with out issue, as follows:

test=#  select tab.dr_prod_date FROM (SELECT ('01/01/0'::text ||
"substring"(ilch.lot_id::text, 5, 1))::date AS dr_prod_date FROM
my_lot_test ilch) AS tab limit 1;
 dr_prod_date 
--------------
 2007-01-01
(1 row)

And using slashes or dashes, or even a full year specification (as shown
by my following query) still gives me the same issue, just in a
different location:

test=# select tab.dr_prod_date FROM (SELECT ('200' ||
substring(ilch.lot_id::text, 5, 1) || '-01-01')::date AS dr_prod_date
FROM  my_lot_test ilch) AS tab where tab.dr_prod_date = '2/5/08' limit
1;
ERROR:  invalid input syntax for type date: "200W-01-01"

the test data I am using for this example is as follows:

CREATE TABLE my_lot_test
( id			SERIAL,
  lot_id		VARCHAR(5),

  PRIMARY		KEY(id));

INSERT INTO my_lot_test(lot_id) VALUES('01025');
INSERT INTO my_lot_test(lot_id) VALUES('01026');
INSERT INTO my_lot_test(lot_id) VALUES('01027');
INSERT INTO my_lot_test(lot_id) VALUES('02027');

Note that the formatting here is unique to my test, but the issue arises
with this any valid combination of string that I have tried, short and
longer.

On Fri, 2008-02-29 at 13:12 -0500, Colin Wetherbee wrote:
> Chris Bowlby wrote:
> > test=# select tab.dr_prod_date FROM 
> > test-# (SELECT ('01/01/0'::text || substring(ilch.lot_id::text, 5,
> > 1))::date AS dr_prod_date FROM my_lot_test ilch) AS tab 
> > test-# where tab.dr_prod_date = '2/5/08' limit 1;
> > ERROR:  invalid input syntax for type date: "01/01/0W"
> 
> Using arbitrary slashes can confuse a lot of things, although I'm not 
> sure why you're getting a W there.  Perhaps you could send us some test 
> data?
> 
> The following works fine for me on 8.1.10.
> 
> cww=# create table foo (mydate text);
> CREATE TABLE
> cww=# insert into foo values ('00001');
> INSERT 0 1
> cww=# insert into foo values ('00002');
> INSERT 0 1
> cww=# insert into foo values ('00003');
> INSERT 0 1
> cww=# select ('200' || substring(mydate, 5, 1) || '-01-01')::date from foo;
>      date
> ------------
>   2001-01-01
>   2002-01-01
>   2003-01-01
> (3 rows)
> 
> Colin

In response to

Responses

pgsql-general by date

Next:From: abracadabudaDate: 2008-02-29 18:28:50
Subject: pgsql structure export to XML
Previous:From: Carl McCallaDate: 2008-02-29 18:12:50
Subject: Removing Users and Revoking Privileges

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group