Hello

I cand find the right syntax for the statement that return the NewDateField

SELECT DateField + a integer of months AS NewDateField

Thanks
Sorin

Sorin,

> I cand find the right syntax for the statement that return the NewDateField
>
> SELECT DateField + a integer of months AS NewDateField

You have to work with intervals. Not the quotes!

select DateField + interval '1' month as NewDateField from ...

Uli

correction:

I meant "note the quotes" not "not the quotes".

Tiny typo, big difference.

Uli

Uli


Nice typo - I think I prefer it to the correct version

Also note that the interval can't be specified as a parameter. If you want to change it you have to change the sql.

Roy Lambert [Team Elevate]

Roy,

> Nice typo - I think I prefer it to the correct version



> Also note that the interval can't be specified as a parameter. If you want to change it you have to change the sql.

In such cases I use something like this:

select Datefield + interval ''' + Cast(MyIntValue as Varchar) + '''
month from MyTable';

Uli

Thanks

That mean that if I want do something like

select DateField, DateField + interval NumberField month as NewDateField...

I can't?

Sorin


> Also note that the interval can't be specified as a parameter. If you want
> to change it you have to change the sql.
>
> Roy Lambert [Team Elevate]
>

Thanks Uli

I try

select DateField + interval ''' +cast(intField as varchar) + ''' month as
NewDateField...

but it raise error #700 before interval

Sorin

This script with a table called "Kalender" and a Date Column called
"Datum" does work:

SCRIPT
BEGIN

   DECLARE Result CURSOR WITH RETURN FOR Stmt;
   DECLARE Delta INTEGER DEFAULT 2;

   PREPARE Stmt FROM
      'select Datum + interval ''' + cast(Delta as varchar) + ''' month as
       NewDateField from Kalender';
   OPEN Result;

END

Uli

Thanks Uli

I need the state in a query not in a script.

edbQuery.sql.text :=  'select Datum + interval ''' + cast(Delta as varchar)
+ ''' month as...

Does it make any difference?


> SCRIPT
> BEGIN
>
>    DECLARE Result CURSOR WITH RETURN FOR Stmt;
>    DECLARE Delta INTEGER DEFAULT 2;
>
>    PREPARE Stmt FROM
>       'select Datum + interval ''' + cast(Delta as varchar) + ''' month as
>        NewDateField from Kalender';
>    OPEN Result;
>
> END
>
> Uli
>

Sorin,

> I need the state in a query not in a script.
>
> edbQuery.sql.text :=  'select Datum + interval ''' + cast(Delta as varchar)
> + ''' month as...
>
> Does it make any difference?

It's the same: just compose the statement like this:

with MyQuery do
begin
  sql.add('select Datum + interval ''' + IntToStr(MyIntValue) + '''
month as NewField from Kalender');
  open;
end;

Uli