date arithmetic

Hi, everybody, I have two fields in a table: (tx_date DATE, tx_time DATETIME HOUR TO SECOND) I need to calculate full DATETIME YEAR TO SECOND value combined from date and time. Is there a way to do that WITHOUT intermediate locale-specific casting to CHAR() - that is, solutions like TO_DATE(tx_date || " " || tx_time, "%m/%d/%Y %T") are not acceptable ------------------------------------------ Alexey Sonkin sending to informix-list

select extend(tx_date, year to second) + tx_time::CHAR(8)::INTERVAL HOUR TO SECOND from somewhere If you want to miss the char cast then you will need to create the UDR cast yourself, AFAIK there is no cast of DATETIME TO INTERVAL However you can do select extend(tx_date, year to second) + (tx_time - extend("00:00:00", HOUR TO SECOND)) from somewhere Alexey Sonkin wrote: > > Hi, everybody, > > I have two fields in a table: > (tx_date DATE, tx_time DATETIME HOUR TO SECOND) > > I need to calculate full DATETIME YEAR TO SECOND value > combined from date and time. > > Is there a way to do that WITHOUT intermediate locale-specific > casting to CHAR() - that is, solutions like > TO_DATE(tx_date || " " || tx_time, "%m/%d/%Y %T") > are not acceptable > > ------------------------------------------ > Alexey Sonkin > > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Substitue the literals with your column names and the time units to those you need and you have it with select extend(datetime(2004-01-01) year to day, year to second ) + ( extend(today, year to second) - extend(datetime(12:34:56) hour to second, year to second) ) from tab1 You can't directly concat two datetimes, but you can add an interval to a datetime; so, the trick is to convert everthing to common units while using pad values that can be subtracted from each other and thereby create an interval out of the smaller (with less significant start time unit) operand. Luck, Chris Alexey Sonkin <alexeis@grandvirtual.com> wrote in message news:<c1g0ok$1qe$1@terabinaries.xmission.com>... > Hi, everybody, > > I have two fields in a table: > (tx_date DATE, tx_time DATETIME HOUR TO SECOND) > > I need to calculate full DATETIME YEAR TO SECOND value > combined from date and time. > > Is there a way to do that WITHOUT intermediate locale-specific > casting to CHAR() - that is, solutions like > TO_DATE(tx_date || " " || tx_time, "%m/%d/%Y %T") > are not acceptable > > ------------------------------------------ > Alexey Sonkin > > sending to informix-list