how to add time portion in a date field

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

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, > > 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 >

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... > --- > 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, > > > 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

And if you want EXACTLY 23 hours, 59 minutes, you could do: SELECT a + 1 units day - 1 units minute, * from tessie Superboer wrote: > 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... >> --- >> 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, >>> 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 >

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] >> Thanks >> Vchu -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200805/1

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: jleffler@earthlink.net, jleffler@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