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 fATEavm71554 for ; Thu, 29 Nov 2001 09:36:57 -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 fATEYXe19063; Thu, 29 Nov 2001 15:34:33 +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: <1007035567.1110.1.camel@entwicklung01.cenes.de> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-qxHDLszLRTizmI+juErp" X-Mailer: Evolution/0.99.2 (Preview Release) Date: 29 Nov 2001 15:36:58 +0100 Message-Id: <1007044618.1107.7.camel@entwicklung01.cenes.de> Mime-Version: 1.0 X-Archive-Number: 200111/410 X-Sequence-Number: 5792 --=-qxHDLszLRTizmI+juErp Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Thu, 2001-11-29 at 15:00, Gary Stainburn wrote: > On Thursday 29 November 2001 12:06 pm, Markus Bertheau wrote: > > 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 > > > dates in the format 'dd/mm/ccyy' which for the days 13-31 for each mo= nth > > > worked fine. > > > > > > The problem I have is that for the days 01-12 for each month, the date > > > was interpretted as 'mm/dd/ccyy'. Now I know about it I need to fix = it. > > > > > > Two questions. > > > > > > 1) how can I select on part of a date? I need to select all records > > > where 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 > > > > > > update calls set xdate =3D ???? where ??????; > > > > What exactly do you want to achieve? >=20 > Thanks for the information. Basically what I want to be able to do is=20 > correct the dates that are wrong by swapping the day and month parts. >=20 > For example, a date entered as 23/11/2000 is correctly stored as 2000/11/= 23=20 > while a date entered as 07/11/2000 (7th Nov 2000) is incorrectly stored a= s=20 > 2000/07/11. I need to be able to access it using the select you gave abo= ve,=20 > and then re-store the date as 2000/11/07. >=20 > something like: >=20 > update calls set date_part('day',xdate) =3D date_part('month',xdate),=20= =20=20=20=20=20=20=20=20=20=20 > date_part('month',xdate) =3D date_part('day',xdate)=20 > where date_part('day',xdate) < 13 and xdate < '2001/11/08'; >=20 > (2001/11/08 is when I found/fixed the insert problem) update calls set xdate =3D date_part('year', data) || '-' || date_part('day', data) || '-' || date_part('month', data) where ...; || is a string concatenation operator. The query above swaps month and day. The query only handles dates. If xdate also contains time information you have to add it. Markus Bertheau --=-qxHDLszLRTizmI+juErp Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQA8BkgKKNEjAb345r4RAiPcAJ41Cb10VODf0f8aW/pGxL+rL3PJfwCcCjrS dppDHK2a4bNa6KsninwqfDE= =7j69 -----END PGP SIGNATURE----- --=-qxHDLszLRTizmI+juErp--