Thread: Re: [SQL] cast text as date

>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

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

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'