Informix: datetime manipulation

Question

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 dateandtime field. How do I do that?

Answer 1

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

Answer 2

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