Обсуждение: How to implement GOMONTH function

I need to create function GOMONTH which returns date by given number of
month before or forward using sql or pgsql in 8.1+
For example,
GOMONTH( DATE '20070513', 1 )  should return date '20070613'
GOMONTH( DATE '20070513', -2 )  should return date '20070313'

I tried

CREATE OR REPLACE FUNCTION public.gomonth(date, integer,
        out date) IMMUTABLE AS
$_$
SELECT $1 + $2'months';
$_$ language sql

but got error

ERROR: syntax error at or near "'months'"

How to implement this ?

Andrus.

On Sun, 13 May 2007, Andrus wrote:

> I need to create function GOMONTH which returns date by given number of
> month before or forward using sql or pgsql in 8.1+ For example, GOMONTH(
> DATE '20070513', 1 ) should return date '20070613' GOMONTH( DATE
> '20070513', -2 ) should return date '20070313'

Andrus,

   I do not know the context for which you need this, but you can use SQL to
calculate DATE + INTERVAL. For example, you could include in a SELECT
statement

   WHERE CURRENT_DATE BETWEEN (DATE '20070513' - INTERVAL '2 month') AND
(DATE '20070513' + INTERVAL '1 month')

   The PostgreSQL docs have an excellent section on temporal data types.
Also, search Google with the string 'Richard T. Snodgras' (at the University
of Arizona) and download the pdf copy of his book, 'Developing Time-Oriented
Database Applications in SQL.' I learned of this book just this morning.

HTH,

Rich

--
Richard B. Shepard, Ph.D.               |    The Environmental Permitting
Applied Ecosystem Services, Inc.        |          Accelerator(TM)
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863

Andrus wrote:
> I need to create function GOMONTH which returns date by given number of
> month before or forward using sql or pgsql in 8.1+
> For example,
> GOMONTH( DATE '20070513', 1 )  should return date '20070613'
> GOMONTH( DATE '20070513', -2 )  should return date '20070313'
>
> I tried
>
> CREATE OR REPLACE FUNCTION public.gomonth(date, integer,
>         out date) IMMUTABLE AS
> $_$
> SELECT $1 + $2'months';

This should read:

$3 := $1 + $2 * INTERVAL '1 month';

I'm not entirely sure about $3, I've never used out parameters before.

> $_$ language sql
>
> but got error
>
> ERROR: syntax error at or near "'months'"
>
> How to implement this ?
>
> Andrus.


--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //