Обсуждение: How to add days to date

Hi:

I have problem trying to add same days to a date.

I have this select :

SELECT '2006-08-01' + FP.carencia + ( FP.prazo * ( MFP.parcela - 1 )) AS
vencimento
  FROM fi_mov_formas_pagamento MFP
       LEFT OUTER JOIN fi_formas_pagamento FP ON ( MFP.idformapagamento =
FP.idformapagamento AND MFP.idempresa = FP.idempresa )
       INNER JOIN hd_cabecalho HDC ON ( MFP.idmovimento = HDC.idhelpdesk AND
MFP.idempresa = HDC.idempresa )
  WHERE MFP.idmovimento = 1
 AND MFP.idempresa = 1
 AND MFP.idtipomovimentacao = 1

'2006-08-01' + FP.carencia + ( FP.prazo * ( MFP.parcela - 1 )) --> This
results in

EX :
'2006-08-01' + 30 + ( 7 * ( 3 - 1 )) ==> '2006-08-01' + 44

All my trys fails.

Can you help me ?

Thanks in advance.

Alejandro Michelin Salmon

On Tue, Aug 15, 2006 at 10:10:27PM -0300, Alejandro Michelin Salomon ( Adinet ) wrote:
> EX :
> '2006-08-01' + 30 + ( 7 * ( 3 - 1 )) ==> '2006-08-01' + 44
>
> All my trys fails.

The error message hints at what's wrong:

test=> SELECT '2006-08-01' + 30 + (7 * (3 - 1));
ERROR:  invalid input syntax for integer: "2006-08-01"

PostgreSQL doesn't know that the untyped string is supposed to be
interpreted as a date.  Use a cast:

test=> SELECT '2006-08-01'::date + 30 + (7 * (3 - 1));
  ?column?
------------
 2006-09-14
(1 row)

or

test=> SELECT CAST('2006-08-01' AS date) + 30 + (7 * (3 - 1));
  ?column?
------------
 2006-09-14
(1 row)

--
Michael Fuhr

Alejandro Michelin Salomon ( Adinet ) wrote:
> Hi:
>
> I have problem trying to add same days to a date.
>
> '2006-08-01' + FP.carencia + ( FP.prazo * ( MFP.parcela - 1 )) --> This
> results in
>
> EX :
> '2006-08-01' + 30 + ( 7 * ( 3 - 1 )) ==> '2006-08-01' + 44

Looks like you could use the interval type here too. I don't know what
the above is supposed to do, but I'd prefer to write it like:

'2006-08-01'::date + '1 month'::interval + (3-1) * '1 week'::interval

Or:
'2006-08-01'::date + INTERVAL '1 month' + (3-1) * INTERVAL '1 week'

It at least saves you the trouble of determining how long what month
takes, and it handles DST changes correctly.
The drawback is that it's not a linear data type, which can cause some
trouble if you need to convert values from your application to intervals.

For details, have a look at the documentation:
http://www.postgresql.org/docs/8.1/static/datatype-datetime.html

--
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 //

Michael :

I change my query to this :

SELECT CAST( '2006-08-01' AS DATE ) + FP.carencia + ( FP.prazo * (
MFP.parcela - 1 )) AS vencimento
  FROM fi_mov_formas_pagamento MFP
       LEFT OUTER JOIN fi_formas_pagamento FP ON ( MFP.idformapagamento =
FP.idformapagamento AND MFP.idempresa = FP.idempresa )
       INNER JOIN hd_cabecalho HDC ON ( MFP.idmovimento = HDC.idhelpdesk AND
MFP.idempresa = HDC.idempresa )
  WHERE MFP.idmovimento = 1
 AND MFP.idempresa = 1
 AND MFP.idtipomovimentacao = 1

And i run ok now.

Thanks for your help.

Alejandro

-->-----Mensagem original-----
-->De: Michael Fuhr [mailto:mike@fuhr.org]
-->Enviada em: terça-feira, 15 de agosto de 2006 22:17
-->Para: Alejandro Michelin Salomon ( Adinet )
-->Cc: Pgsql-General
-->Assunto: Re: [GENERAL] How to add days to date
-->
-->
-->On Tue, Aug 15, 2006 at 10:10:27PM -0300, Alejandro Michelin
-->Salomon ( Adinet ) wrote:
-->> EX :
-->> '2006-08-01' + 30 + ( 7 * ( 3 - 1 )) ==> '2006-08-01' + 44
-->>
-->> All my trys fails.
-->
-->The error message hints at what's wrong:
-->
-->test=> SELECT '2006-08-01' + 30 + (7 * (3 - 1));
-->ERROR:  invalid input syntax for integer: "2006-08-01"
-->
-->PostgreSQL doesn't know that the untyped string is supposed
-->to be interpreted as a date.  Use a cast:
-->
-->test=> SELECT '2006-08-01'::date + 30 + (7 * (3 - 1));
-->  ?column?
-->------------
--> 2006-09-14
-->(1 row)
-->
-->or
-->
-->test=> SELECT CAST('2006-08-01' AS date) + 30 + (7 * (3 - 1));
-->  ?column?
-->------------
--> 2006-09-14
-->(1 row)
-->
-->--
-->Michael Fuhr
-->