Re: [GENERAL] How to add days to date

2006-08-16 Thread Alban Hertroys 

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
[EMAIL PROTECTED]

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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

RES: [GENERAL] How to add days to date

2006-08-16 Thread Alejandro Michelin Salomon \( Adinet \) 
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:[EMAIL PROTECTED] 
--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
--


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] How to add days to date

2006-08-15 Thread Alejandro Michelin Salomon \( Adinet \) 
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


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] How to add days to date

2006-08-15 Thread Michael Fuhr 
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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster