Re: search for partial dates

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 excellent replies you've got, you can always do
some explicit casting and produce interesting things like:

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;

for example.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/