pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Markus Bertheau <twanger@bluetwanger.de>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Fixing faulty dates - select on day part of a date field
Date: 29 Nov 2001 15:36:58 +0100
Message-ID: <1007044618.1107.7.camel@entwicklung01.cenes.de> (raw)
In-Reply-To: <E169RhA-0006sD-00@stan.ringways.co.uk>
References: <E169PMt-0005zg-00@stan.ringways.co.uk>
	<1007035567.1110.1.camel@entwicklung01.cenes.de>
	<E169RhA-0006sD-00@stan.ringways.co.uk>

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 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)

update calls set xdate = 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

view thread (7+ messages)  latest in thread

Message-ID: <1007044618.1107.7.camel@entwicklung01.cenes.de>
Permalink:  ../1007044618.1107.7.camel@entwicklung01.cenes.de/
Also on:    postgresql.org/message-id/1007044618.1107.7.camel@entwicklung01.cenes.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: twanger@bluetwanger.de, gary.stainburn@ringways.co.uk
  Subject: Re: Fixing faulty dates - select on day part of a date field
  In-Reply-To: <1007044618.1107.7.camel@entwicklung01.cenes.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox