Informix: data processing over time

Question

I have 2 fields in table test1:

onlydate DATE
onlytime DATETIME HOUR TO MINUTE

and 1 field in table test2:

dateandtime DATETIME YEAR TO SECOND

Now I need to add the value of the onlydate and onlytime field and set it to the dateandtime field. How to do it?

Answer 1

Main problems:

• Converting DATE to DATETIME - Using EXTEND.
• Converting DATETIME to INTERVAL - using subtraction.

Assembling these two concepts and applying only to SELECT:

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;

As a result, you want:

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

Subtracting midnight from timeonlyconverts it to INTERVAL HOUR TO MINUTE; you can add DATETIME YEAR TO SECOND and INTERVAL HOUR TO MINUTE to get DATETIME YEAR TO SECOND. You cannot add two DATETIME values.

, , :

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

( DBDATE = Y4MD- , , . DATE DBDATE, MDY(5,31,2010).)

Answer 2

datetime, :

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