Re: search for partial dates

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: search for partial dates
Date: 2009-06-12 18:38:44
Message-ID: 20090612183844.GY5407@samason.me.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Christine Penner 2009-06-12 19:36:27 String Manipulation
Previous Message Francisco Figueiredo Jr. 2009-06-12 18:04:09 Re: Behavior of NpgsqlDataReader in NpgSql 1 and NpgSql 2