From: | Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
---|---|
To: | lynch(at)lscorp(dot)com (Richard Lynch), pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] cast text as date |
Date: | 1998-06-17 08:21:09 |
Message-ID: | l03110700b1ad29f0fde1@[147.233.159.109] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Patrice Hédé | 1998-06-17 11:41:13 | Re: [SQL] Internationalisation: SELECT str (ignoring Umlauts/Accents) |
Previous Message | Andreas Fredriksson | 1998-06-17 07:01:12 | subscribe pgsql-sql |