Hiii

I want to get proper date after add Interval. I have also try with below query, but i had not got any proper result.

Query :
select date '2011-06-30' + interval '6 months';

If i have run this query i got the below output.
Output:
2011-12-30 00:00:00

Generally if i have add 6 months in '2011-06-30' then correct output expect from query is '2011-06-31'.

Why it happen here..? How can i get proper output from query.. ?

-- Hiren Lad..

It is automatically changing the output data type to timestamp (see examples here). You can override the cast if you list, e.g.:

select (date '2011-06-30' + interval '6 months')::date;

Hello someidiot,

Here some mistake in my post.. i have again mention my question.

Query :
select date '2011-06-30' + interval '6 months';

If i have run this query i got the below output.
Output:
2011-12-30 00:00:00

Generally if i have add 6 months in '2011-06-30' then correct output expect from query is '2011-12-31 00:00:00'.

Is it any idea ?

-- Hiren Lad

Oh sorry, I miss-interpreted the question.

Why would you expect that result? 6 months has no definite length, it depends which months. The doco says: "Note there can be ambiguity in the months returned by age because different months have a different number of days. PostgreSQL's approach uses the month from the earlier of the two dates when calculating partial months. For example, age('2004-06-01', '2004-04-30') uses April to yield 1 mon 1 day, while using May would yield 1 mon 2 days because May has 31 days, while April has only 30."

But that would give 27th Dec using June's 30 days * 6 = 180 days.

It looks like it used the avg number of days in a month rounded up to 183.

I understand what you are saying but we never had this issues when we worked with other RDBMS, SQL Server for instance. Anyways, I think that is irrelevant, coming back to point, I would like to know if there is some way to achieve what I want in Postgre? I want 31st of December if I add 6 months to 30th June.