Received: from leela.one-2-one.net (leela.one-2-one.net [217.115.142.65]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fATC68m53750 for ; Thu, 29 Nov 2001 07:06:08 -0500 (EST) (envelope-from twanger@bluetwanger.de) Received: from [192.168.0.14] (pD9501213.dip.t-dialin.net [217.80.18.19]) by leela.one-2-one.net (8.11.0/8.11.0) with ESMTP id fATC3fe30141; Thu, 29 Nov 2001 13:03:41 +0100 Subject: Re: Fixing faulty dates - select on day part of a date field From: Markus Bertheau To: Gary Stainburn Cc: pgsql-sql In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-C2J3dDYNj/MqakCsunr5" X-Mailer: Evolution/0.99.2 (Preview Release) Date: 29 Nov 2001 13:06:06 +0100 Message-Id: <1007035567.1110.1.camel@entwicklung01.cenes.de> Mime-Version: 1.0 X-Archive-Number: 200111/405 X-Sequence-Number: 5787 --=-C2J3dDYNj/MqakCsunr5 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Thu, 2001-11-29 at 12:31, Gary Stainburn wrote: > I've got a problem with dates on one of my tables. I've been inserting da= tes=20 > in the format 'dd/mm/ccyy' which for the days 13-31 for each month worked= =20 > fine. >=20 > The problem I have is that for the days 01-12 for each month, the date wa= s=20 > interpretted as 'mm/dd/ccyy'. Now I know about it I need to fix it. >=20 > Two questions. >=20 > 1) how can I select on part of a date? I need to select all records wher= e=20 > the day is not > 12. select date_part('part', attribute) where part is one of week, day, year ans so on standards compliant way is select extract(part from attribute) so: select * from table where date_part('day', attribute) < 13; > 2) can I do this in a single update, i.e. can I do something around >=20 > update calls set xdate =3D ???? where ??????; What exactly do you want to achieve? Markus Bertheau --=-C2J3dDYNj/MqakCsunr5 Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQA8BiSuKNEjAb345r4RAmy/AJ9PsI9Uq+xXb2lg6XaRktfbkWQNjACfVhsx rkvxk6bMYYVWE/MlK9tHDw0= =GNS4 -----END PGP SIGNATURE----- --=-C2J3dDYNj/MqakCsunr5--