My table has 2 columns:
Reg_Date date, and Day_Num integer.

I need to add Day_Num to Reg_Date and get a date.

I know I can use:

  select reg_date + interval '5' day from table1


but how do I replace '5' with Day_Num? The Interval expects a constant and the following won't work:

  select reg_date + interval day_num day from table1

TIA
Barry

Barry



select reg_date + CAST(day_num AS INTERVAL day) from table1


Roy Lambert [Team Elevate]

Roy,
    Thanks, that worked.

1) What confuses me is this syntax works:

 select date '2012-01-01' + interval '5'  day from table1

2) but replacing the '5' with an integer column name, won't work:

 select date '2012-01-01' + interval day_num  day from table1

3) As you pointed out, I have to change the syntax of the interval phrase to:

 select date '2012-01-01' + cast(day_num as interval day) from table1

So unless I am adding a string constant, I will have to use Cast(). There is nothing wrong with that, but why does #1 work? I would have thought the DBMS would have known day_num was an integer and substituted that in for the constant '5'. Two different syntaxes for date arithmetic is a puzzle that I am trying to wrap my head around.

BTW, I looked through the ElevateDb pdf manuals/help before posting yesterday, and couldn't find any interval arithmetic that used column names. They only used constants. So I would have been stuck here for a while if you hadn't come along.

Barry
v2.09

Barry,

This syntax is SQL standard syntax, not some weird EDB proprietary syntax.
INTERVAL '5' DAY is the standard syntax to represent a literal of type INTERVAL DAY, and CAST is also the standard SQL function to convert types.

What happens here is your column "day_num" is not of type INTERVAL DAY but of type INTEGER, and that's why you have to convert the integer column to the correct type using CAST( ) before adding it to a date.
If "day_num" was of type INTERVAL DAY you wouldn't have to use CAST to convert types , you would be allowed to write:
 
SELECT DATE '2012-01-01' + day_num FROM table1

So, in fact there aren't 2 different syntax rules, what happens is just the need to convert one type to another using the CAST function, thats all.

--
Fernando Dias
[Team Elevate]

Fernando,
     Thanks for the explanation. I didn't realize I could create a column type of "Interval Day". That solves the problem of using Cast().

BTW, why is there no "Interval Week" column type? There are a lot of things that are measured in weeks.

TIA
Barry

Barry,

<<why is there no "Interval Week" column type?>>

I don't know, perhaps because INTERVAL '1' WEEK would be the same as INTERVAL '7' DAY.

About the interval types, you can find all the interval types here:
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Interval_Types

--
Fernando Dias
[Team Elevate]

Barry,

<< 1) What confuses me is this syntax works:

 select date '2012-01-01' + interval '5'  day from table1

2) but replacing the '5' with an integer column name, won't work:

select date '2012-01-01' + interval day_num  day from table1 >>

Column references have their own type, etc. whereas '5' would be considered
just a normal string without the INTERVAL modifier in front of it.  IOW, the
INTERVAL, DATE, TIME, and TIMESTAMP modifiers are simply a way to tell the
SQL compiler "Hey, this isn't just a string, it's something else".
Therefore, they are of little use for columns/parameters that have their own
type already, and are invalid when you attempt to use them for type
conversion instead of CAST.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com