Tim

I had a play with the interval functions (thinking about Harry's post) and found that MONTH and YEAR don't work and the rest all give the same result using the sql below.

select _date,cast((_date - date'2007-01-01') as interval day)
,cast((_date - date'2007-01-01') as interval minute)
,cast((_date - date'2007-01-01') as interval second)
,cast((_date - date'2007-01-01') as interval msecond)
  from transactions

On the basis I know nothing about these weird things it may be dead right.

Roy Lambert

Roy,

<< I had a play with the interval functions (thinking about Harry's post)
and found that MONTH and YEAR don't work and the rest all give the same
result using the sql below. >>

a) What does your MONTH and YEAR interval query look like ?

b) You must CAST the result into a string representation, otherwise you will
see the native INTERVAL representation (In64):

select _date,cast(cast((_date - date'2007-01-01') as interval day) as
varchar(20))....

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>a) What does your MONTH and YEAR interval query look like ?

================================================================================
SQL Error (ElevateDB 1.05 Build 2)
================================================================================

ElevateDB Error #700 An error was found in the statement at line 1 and column 28
(Expected Char, VarChar, Byte, VarByte, SmallInt, Integer, BigInt, Interval
Year or Interval Year To Month expression but instead found ("_date" - DATE
'2007-01-01'))


>b) You must CAST the result into a string representation, otherwise you will
>see the native INTERVAL representation (In64):
>
> select _date,cast(cast((_date - date'2007-01-01') as interval day) as
>varchar(20))....

Cor - its all obvious this stuff ain't it

Roy Lambert

Roy,

<< ElevateDB Error #700 An error was found in the statement at line 1 and
column 28
(Expected Char, VarChar, Byte, VarByte, SmallInt, Integer, BigInt, Interval
Year or Interval Year To Month expression but instead found ("_date" - DATE
'2007-01-01')) >>

Use this instead:

select _date,
(_date - date'2007-01-01') year,
(_date - date'2007-01-01') year to month,
(_date - date'2007-01-01') month,
.......

http://www.elevatesoft.com/edb1sql_arithmetic_operators.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I have a horrible feeling I'm going to have to print the manual out and read it

Roy Lambert