SQL: How to substract 1 day from a daytime field ?
Hallo.
We're running Informix Online Dynamic Server 7.2 on Win NT 4.0.
I was not able to substract 1 day from a daytime ('dttermination')
field. For
example the value is "01.11.1999" and one day should be subtracted. The
result is "31.10.1999".
Trying the following does not work.
update TABLE set dttermination = extend (dttermination, day to second) -datetime (1) day to day where idTABLE =1;
OR
update TABLE set dttermination = extend (dttermination, day to day ) -datetime (1) day to day where idTABLE =1;
This gives the error message
General SQL error: It is not possible to convert between the specified
types.
Can anyone help me?
Thank you in advance.
Sent via Deja.com http://www.deja.com/
Before you buy.
>
> I was not able to substract 1 day from a daytime ('dttermination')
> field. For
> example the value is "01.11.1999" and one day should be subtracted.
The
> result is "31.10.1999".
>
> Trying the following does not work.
> update TABLE set dttermination = extend (dttermination, day to second)-
> datetime (1) day to day where idTABLE =1;
> OR
> update TABLE set dttermination = extend (dttermination, day to day ) -> datetime (1) day to day where idTABLE =1;
try 'update TABLE set dttermination = dttermination - 1 units day
where...
--
# unrm /
ksh: unrm: not found
# man cpio
Sent via Deja.com http://www.deja.com/
Before you buy.
Oliver Thinnes wrote:
>
> Hallo.
>
> We're running Informix Online Dynamic Server 7.2 on Win NT 4.0.
>
> I was not able to substract 1 day from a daytime ('dttermination')
> field. For
> example the value is "01.11.1999" and one day should be subtracted. The
> result is "31.10.1999".
> Trying the following does not work.
> update TABLE set dttermination = extend (dttermination, day to second) -> datetime (1) day to day where idTABLE =1;
> OR
> update TABLE set dttermination = extend (dttermination, day to day ) -> datetime (1) day to day where idTABLE =1;
>
> This gives the error message
> General SQL error: It is not possible to convert between the specified
> types.
Yes it does. Should be:
update TABLE set dttermination = extend (dttermination, day to second) -
INTERVAL(1) day(1) TO day;
You cannot subtract a DATETIME from a DATETIME and put the result into a
DATETIME column. The results of the difference between two DATETIMEs is
defined as an INTERVAL which is incompatible with the column being updated.
Therefore you have to subtract an INTERVAL from a DATETIME to result in a
DATETIME.
Art S. Kagel