Re: [GENERAL] search for partial dates

2009-06-12 Thread Scott Marlowe
On Fri, Jun 12, 2009 at 7:58 AM, James B. Byrne wrote: > > On Thu, June 11, 2009 17:37, Andy Colson wrote: > >> That's a little vague, so how about: >> >> select * from somethine where (extract(year from idate) = $1) or >> (extract(year from idate) = $2 and extract(month from idate) = $3) >> or (ex

Re: [GENERAL] search for partial dates

2009-06-12 Thread Sam Mason
On Fri, Jun 12, 2009 at 12:47:26AM +0200, Leif B. Kristensen wrote: > CREATE OR REPLACE FUNCTION date2text(DATE) RETURNS TEXT AS $$ > -- removes hyphens from a regular date > SELECT > SUBSTR(TEXT($1),1,4) || > SUBSTR(TEXT($1),6,2) || > SUBSTR(TEXT($1),9,2) > $$ LANGUAGE sql STABLE; Why

Re: [GENERAL] search for partial dates

2009-06-12 Thread James B. Byrne
On Thu, June 11, 2009 17:37, Andy Colson wrote: > That's a little vague, so how about: > > select * from somethine where (extract(year from idate) = $1) or > (extract(year from idate) = $2 and extract(month from idate) = $3) > or (extract(year from idate) = $4 and extract(month from idate) = $5 >

Re: [GENERAL] search for partial dates

2009-06-11 Thread Leif B. Kristensen
On Thursday 11. June 2009, James B. Byrne wrote: >Given a datetime column, not null, is there a single syntax that >permits searching for all dates in a given year, year+month, and >year+month+day such that a single parameterised query can handle all >three circumstances? Apart from the other exce

Re: [GENERAL] search for partial dates

2009-06-11 Thread Andy Colson
James B. Byrne wrote: Given a datetime column, not null, is there a single syntax that permits searching for all dates in a given year, year+month, and year+month+day such that a single parameterised query can handle all three circumstances? That's a little vague, so how about: select * from

Re: [GENERAL] search for partial dates

2009-06-11 Thread Scott Marlowe
On Thu, Jun 11, 2009 at 2:35 PM, Tom Lane wrote: > "James B. Byrne" writes: >> Given a datetime column, not null, is there a single syntax that >> permits searching for all dates in a given year, year+month, and >> year+month+day such that a single parameterised query can handle all >> three circu

Re: [GENERAL] search for partial dates

2009-06-11 Thread Tom Lane
"James B. Byrne" writes: > Given a datetime column, not null, is there a single syntax that > permits searching for all dates in a given year, year+month, and > year+month+day such that a single parameterised query can handle all > three circumstances? Try date_trunc() ... however, if you want th

Re: [GENERAL] search for partial dates

2009-06-11 Thread Christophe
On Jun 11, 2009, at 1:23 PM, James B. Byrne wrote: Given a datetime column, not null, is there a single syntax that permits searching for all dates in a given year, year+month, and year+month+day such that a single parameterised query can handle all three circumstances? Well, of course, in a