Thread

1. Re: [SQL] cast text as date

   Richard Lynch <lynch@lscorp.com> — 1998-06-16T17:58:49Z

   >At 11:14 +0300 on 16/6/98, Richard Lynch wrote:
   >
   >
   >> I've never created a function of my own, and maybe that's the way to go,
   >> but I'm not sure where to start...
   >> The strings in question actually only have a month/year (no date), if that
   >> matters (they're expirations)...
   >> The resulting date can just default to 1 for the date.
   >
   >What version of Postgres, and what error, exactly, did it report?
   
   ERROR:  function date(text) does not exist
   
   I don't know what version because my ISP installed it, and there seems to
   be no file I can read that tells me, and postmaster -v isn't defined.
   
   There are two postgresql directories.  One is labeled 6.2.1
   The other is just pgsql.
   
   The ISP changed psql very recently to require me to use -u and an login
   name and password, when they moved their software to a shiny new box, and
   (I think) upgraded postgresql, if that is any help at all in identifying
   the version.
   
   --
   --
   -- "TANSTAAFL" Rich lynch@lscorp.com
   
   
   
   

2. Re: [SQL] cast text as date

   Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-06-17T08:21:09Z

   At 20:58 +0300 on 16/6/98, Richard Lynch wrote:
   
   
   >
   > ERROR:  function date(text) does not exist
   >
   > I don't know what version because my ISP installed it, and there seems to
   > be no file I can read that tells me, and postmaster -v isn't defined.
   >
   > There are two postgresql directories.  One is labeled 6.2.1
   > The other is just pgsql.
   
   Oh, I see. Well, you shouldn't use 'date'. It's a limited datatype. I
   always use 'datetime' - it has much more functionality.
   
   Now here is an example for you:
   
   testing=> \d example3
   
   Table    = example3
   +--------------------------+------------------------+-------+
   |           Field          |          Type          | Length|
   +--------------------------+------------------------+-------+
   | mon_year                 | text                   |   var |
   +--------------------------+------------------------+-------+
   
   testing=> SELECT * FROM example3;
   mon_year
   --------
   05/98
   06/99
   12/98
   01/99
   (4 rows)
   
   testing=> SET DATESTYLE TO 'european';
   SET VARIABLE
   
   testing=> SELECT datetime( '01/'::text || mon_year )
   testing-> FROM example3;
   datetime
   ----------------------------
   Fri 01 May 00:00:00 1998 IDT
   Tue 01 Jun 00:00:00 1999 IDT
   Tue 01 Dec 00:00:00 1998 IST
   Fri 01 Jan 00:00:00 1999 IST
   (4 rows)
   
   I think this is what you wanted: You append the strings '01/' with the
   month-and-year field, convert to datetime, and that is comparable as a
   date. For example, here you select the row which has already expired:
   
   testing=> SELECT *
   testing-> FROM example3
   testing-> WHERE 'now' > datetime( '01/'::text || mon_year );
   mon_year
   --------
   05/98
   (1 row)
   
   (Of course the result would have been more interesting if there were a few
   more fields...).
   
   You can define the above conversion as an SQL function for your convenience:
   
   testing=> CREATE FUNCTION monyear2datetime( text ) RETURNS datetime
   testing-> AS 'SELECT datetime( ''01/''::text || $1 )'
   testing-> LANGUAGE 'sql';
   CREATE
   
   testing=> SELECT mon_year, monyear2datetime( mon_year )
   testing-> FROM example3;
   mon_year|monyear2datetime
   --------+----------------------------
   05/98   |Fri 01 May 00:00:00 1998 IDT
   06/99   |Tue 01 Jun 00:00:00 1999 IDT
   12/98   |Tue 01 Dec 00:00:00 1998 IST
   01/99   |Fri 01 Jan 00:00:00 1999 IST
   (4 rows)
   
   Herouth
   Herouth
   
   --
   Herouth Maoz, Internet developer.
   Open University of Israel - Telem project
   http://telem.openu.ac.il/~herutma
   
   
   
   

3. Re: [SQL] cast text as date

   jose' soares <sferac@bo.nettuno.it> — 1998-06-17T12:23:51Z

   On Tue, 16 Jun 1998, Richard Lynch wrote:
   
   > >At 11:14 +0300 on 16/6/98, Richard Lynch wrote:
   > >
   > >
   > >> I've never created a function of my own, and maybe that's the way to go,
   > >> but I'm not sure where to start...
   > >> The strings in question actually only have a month/year (no date), if that
   > >> matters (they're expirations)...
   > >> The resulting date can just default to 1 for the date.
   > >
   > >What version of Postgres, and what error, exactly, did it report?
   > 
   > ERROR:  function date(text) does not exist
   > 
   Is possible convert a DATETIME into:
       
                       abstime
   		    date
   		    text
   	            time
   
   prova=> \d tab
   
   Table    = tab
   +------------------------------+----------------------------------+-------+
   |          Field               |              Type                | Length|
   +------------------------------+----------------------------------+-------+
   | b                            | datetime                         |     8 |
   +------------------------------+----------------------------------+-------+
   
   prova=> select cast(b as text) from tab;
   text
   ----------------------
   1998-12-12 00:00:00+01
   (1 row)
   
   prova=> select cast(b as date) from tab;
   date
   ----------
   1998-12-12
   (1 row)
   
   prova=> select cast(b as time) from tab;
   time
   --------
   00:00:00
   (1 row)
   prova=> select cast(b as abstime) from tab;
   abstime
   ----------------------
   1998-12-12 00:00:00+01
   (1 row)
   
   > I don't know what version because my ISP installed it, and there seems to
   > be no file I can read that tells me, and postmaster -v isn't defined.
   > 
   > There are two postgresql directories.  One is labeled 6.2.1
   > The other is just pgsql.
   > 
   > The ISP changed psql very recently to require me to use -u and an login
   > name and password, when they moved their software to a shiny new box, and
   > (I think) upgraded postgresql, if that is any help at all in identifying
   > the version.
   > 
                                              Jose'