Re: search for partial dates

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 not use the to_char function[1]:

  SELECT to_char($1,'YYYYMMDD');

This is better because TEXT(dateval) doesn't have to give a string back
in the form YYYY-MM-DD, it just does by default.  Readability also seems
to improve when using to_char.

--
  Sam  http://samason.me.uk/

 [1] http://www.postgresql.org/docs/current/static/functions-formatting.html