how to add time portion in a date field

[vchu via DBMonster.com]

In informix,

I have a date field (year to day) only in a table, I want add 23 hrs and 59
mintues and show it in the output. do we have somthing like this??

select extend(c.date, year to day) + "23:59" from contract c;

p.s. c.date is year to day only

Thanks
Vchu

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/informix/200805/1

[Holger de Wall]

from
http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqlr.doc/sqlr150.htm
---
For example, you cannot subtract an INTERVAL MINUTE TO MINUTE value from
the DATETIME value .... You can, however, use the EXTEND function to
perform this calculation, as the following example shows:

EXTEND (DATETIME (2008-8-1) YEAR TO DAY, YEAR TO MINUTE)
- INTERVAL (720) MINUTE(3) TO MINUTE

Result: DATETIME (2008-07-31 12:00) YEAR TO MINUTE
--

Holger de Wall

vchu via DBMonster.com schrieb:

[Superboer]

sounds like you want to add a day:

create table tessie ( a datetime year to day) ;
insert into tessie values (current);

select a + 1 units day, * from tessie

Superboer.

On 16 mei, 00:34, Holger de Wall <hol...@dewall-net.de> wrote:
> fromhttp://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic...

[tgirsch]

And if you want EXACTLY 23 hours, 59 minutes, you could do:

SELECT a + 1 units day - 1 units minute, * from tessie

[vchu via DBMonster.com]

it is ok if i do this:
select EXTEND (DATETIME (2008-8-1) YEAR TO DAY, YEAR TO MINUTE) - INTERVAL
(720) MINUTE(3) TO MINUTE from contract_ref_num where contract_ref_num =
"10003"

but if I replace 2008-8-1 to ref_date, it is not working
it said "non-numeric character in datetime or interval"

select EXTEND (DATETIME (ref_date) YEAR TO DAY, YEAR TO MINUTE) - INTERVAL
(720) MINUTE(3) TO MINUTE from contract_ref_num where contract_ref_num =
"10003"

please helps

ref_date is a date with length = 10, year to day only

Holger de Wall wrote:
>from
>http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqlr.doc/sqlr150.htm
>---
>For example, you cannot subtract an INTERVAL MINUTE TO MINUTE value from
>the DATETIME value .... You can, however, use the EXTEND function to
>perform this calculation, as the following example shows:
>
>EXTEND (DATETIME (2008-8-1) YEAR TO DAY, YEAR TO MINUTE)
> - INTERVAL (720) MINUTE(3) TO MINUTE
>
>Result: DATETIME (2008-07-31 12:00) YEAR TO MINUTE
>--
>
>Holger de Wall
>
>vchu via DBMonster.com schrieb:
>> In informix,
>>

>[quoted text clipped - 7 lines]

[Jonathan Leffler]

vchu via DBMonster.com wrote:
> it is ok if i do this:
> select EXTEND (DATETIME (2008-8-1) YEAR TO DAY, YEAR TO MINUTE) - INTERVAL
> (720) MINUTE(3) TO MINUTE from contract_ref_num where contract_ref_num =
> "10003"
>
> but if I replace 2008-8-1 to ref_date, it is not working
> it said "non-numeric character in datetime or interval"
>
> select EXTEND (DATETIME (ref_date) YEAR TO DAY, YEAR TO MINUTE) - INTERVAL
> (720) MINUTE(3) TO MINUTE from contract_ref_num where contract_ref_num =
> "10003"

EXTEND will automatically convert a DATE to a DATETIME YEAR TO DAY, so
you only need to write:

SELECT EXTEND(ref_date, YEAR TO MINUTE) - INTERVAL(720) MINUTE(3) TO
MINUTE FROM ...

The best way to think of the parentheses after DATETIME is as a funny
way of writing quotes for a DATETIME literal - rather than as the
parentheses of a function call. The corresponding standard SQL
notations are DATE '2008-08-01' and TIME '23:12:01' and TIMESTAMP
'2008-08-01 23:12:01'.

--
Jonathan Leffler #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/

publictimestamp.org/ptb/PTB-3261 sha160 2008-05-17 03:00:03
AE25A5E22AF05851246C636C531BA0D4FC26997C