Query Casting Help

On Tuesday 09 Jul 2002 12:21 am, Hunter Hillegas wrote:

I am trying to execute this little bit:

(extract(month from current_date) + interval '1 month')

The system says I need to cast because it can't figure out how to add
these... I read through the manual and I am still a little confused.

Try:

select extract(month from (current_date + interval '1 month'));
date_part
-----------
8

You want to add an interval to a date. It probably doesn't make sense to add
an interval to a month.

Alternatively:

select extract(month from (current_date)) + 1;

But that wouldn't deal with wrap-around in December.

- Richard Huxton

(extract(month from current_date) + interval '1 month')
The system says I need to cast because it can't figure out how to add
these... I read through the manual and I am still a little confused.
Which side should I be casting? To what datatype?

What are you hoping to get as a result? A date, or an interval? The
extract() function returns a double-precision value for the field (the
month in this case) so doesn't give anything for the interval to work
with.

If you want a date, then you might want something like

(date_trunc('month', current_date) + interval '1 month')

hth

- Thomas

At 7/8/02 11:51 PM, Hunter Hillegas wrote:

I am trying to execute this little bit:

(extract(month from current_date) + interval '1 month')

The system says I need to cast because it can't figure out how to add
these... I read through the manual and I am still a little confused.

The parentheses aren't in quite the right place. Try:

extract(month from (current_date + interval '1 month'))

That is, you want to add 1 month to the current date first (adding a date
and an interval), then extract the month number from the result of that.

Your version extracted the month integer first -- "8" -- then tried
adding an interval to it, but it doesn't make sense to add integers and
intervals together.

------------------------------------
Robert L Mathews, Tiger Technologies