Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.1/8.9.1) with ESMTP id IAA20037 for ; Fri, 27 Nov 1998 08:11:17 -0500 (EST) (envelope-from sferac@bo.nettuno.it) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id OAA17437; Fri, 27 Nov 1998 14:11:11 +0100 (MET) Received: from blu.sferacarta.com (mail.sferacarta.com) [10.20.30.12] by proxy.sferacarta.com with smtp (Exim 2.05 #1 (Debian)) id 0zjOgg-0004ez-00; Fri, 27 Nov 1998 14:15:58 +0000 Date: Fri, 27 Nov 1998 13:23:31 +0100 From: Sferacarta Software X-Mailer: The Bat! (v1.017) UNREG Reply-To: Sferacarta Software Organization: Sferacarta Software Priority: Normal Message-ID: <19557.981127@bo.nettuno.it> To: pgsql-sql@postgresql.org, Stuart Rison Subject: Re: [SQL] Typecasting datetype as date. How do I cope with NULLs? References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Hello Stuart, giovedì, 26 novembre 98, you wrote: SR> Dear All, SR> For those of you who don't want to wade through the details, here's the SR> question: "How do I get the date portion of a datetime field for ALL SR> ENTRIES in a table regardless of whether the entry is NULL or not? (N.B. SR> Typecasting a datetime NULL as date generates an error)" Table = a +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | dt | datetime | 8 | | i | int4 | 4 | +----------------------------------+----------------------------------+-------+ prova=> select * from a; dt |i ----------------------+- 1998-12-26 16:35:23+01|1 1998-12-25 00:00:00+01|2 1998-12-27 00:00:00+01|3 (3 rows) prova=> insert into a values (null,4); INSERT 553697 1 prova=> select * from a; dt |i ----------------------+- 1998-12-26 16:35:23+01|1 1998-12-25 00:00:00+01|2 1998-12-27 00:00:00+01|3 |4 (4 rows) prova=> select *, extract(day from dt) from a; dt |i|date_part ----------------------+-+--------- 1998-12-26 16:35:23+01|1| 26 1998-12-25 00:00:00+01|2| 25 1998-12-27 00:00:00+01|3| 27 |4| (4 rows) -Jose'-