Informix: datetime manipulation

I have 2 fields in a table test1:

onlydate DATE
onlytime DATETIME HOUR TO MINUTE

and 1 field in a table test2:

dateandtime DATETIME YEAR TO SECOND

Now I need to append the value of onlydate and onlytime field and set it to dateand开发者_如何学Ctime field. How do I do that?

The basic issues are:

1. Converting DATE to DATETIME -- use EXTEND.
2. Converting DATETIME to INTERVAL -- use subtraction.

Assembling these two concepts and applied to SELECT only:

create temp table td(dateonly date not null, timeonly datetime hour to minute);
insert into td values('2010-05-31', '06:30');
select extend(dateonly, year to second) + 
       (timeonly - datetime(00:00) hour to minute) from td;

The result is what you want:

DATETIME YEAR TO SECOND
2010-05-31 06:30:00

Subtracting midnight from timeonly converts it into an INTERVAL HOUR TO MINUTE; you can add a DATETIME YEAR TO SECOND and an INTERVAL HOUR TO MINUTE, getting a DATETIME YEAR TO SECOND. You cannot add two DATETIME values.

So, strictly answering your question, you'd write:

INSERT INTO Test2(DateAndTime)
     SELECT EXTEND(DateOnly, YEAR TO SECOND) + 
                  (TimeOnly - DATETIME(00:00) HOUR TO MINUTE) AS DateAndTime
       FROM Test1;

(I run with DBDATE=Y4MD- so that the date literal shown works as expected. To insert the DATE constant reliably regardless of the setting of DBDATE, use MDY(5,31,2010).)

You can concatenate both values as text, and cast it to datetime like:

update datetime_test
   set dateandtime = (dateonly || ' ' || timeonly || ':00')::
                      datetime year to second