Received: from stan.ringways.co.uk ([213.38.87.130]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fATE1wm68449 for ; Thu, 29 Nov 2001 09:01:59 -0500 (EST) (envelope-from gary.stainburn@ringways.co.uk) Received: from gary.ringways.co.uk ([10.1.1.2] helo=there) by stan.ringways.co.uk with smtp (Exim 3.33 #2) id 169RhA-0006sD-00; Thu, 29 Nov 2001 13:57:44 +0000 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: Markus Bertheau Subject: Re: Fixing faulty dates - select on day part of a date field Date: Thu, 29 Nov 2001 14:00:29 +0000 X-Mailer: KMail [version 1.3.1] Cc: pgsql-sql References: <1007035567.1110.1.camel@entwicklung01.cenes.de> In-Reply-To: <1007035567.1110.1.camel@entwicklung01.cenes.de> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: X-Archive-Number: 200111/408 X-Sequence-Number: 5790 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 month > > 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 = ???? where ??????; > > What exactly do you want to achieve? Thanks for the information. Basically what I want to be able to do is correct the dates that are wrong by swapping the day and month parts. For example, a date entered as 23/11/2000 is correctly stored as 2000/11/23 while a date entered as 07/11/2000 (7th Nov 2000) is incorrectly stored as 2000/07/11. I need to be able to access it using the select you gave above, and then re-store the date as 2000/11/07. something like: update calls set date_part('day',xdate) = date_part('month',xdate), date_part('month',xdate) = date_part('day',xdate) where date_part('day',xdate) < 13 and xdate < '2001/11/08'; (2001/11/08 is when I found/fixed the insert problem) > > Markus Bertheau -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000