Re: DATE & TIME
On Tue, 9 Dec 1997, PRAVEEN MOHANAN wrote:
> I have a problem here. Is there a way to add a time to a date.
> For ex. I have 2 fields last_dt of type Date & last_tm of type DATETIME
> HOUR TO MINUTE. Now i want to add this to make a single field like
> 1/1/1996 03:00. I tried the functions like extend, interval.
> In oracle u can concatenate the fileds and use to_date function. Is
> there any function like to_date in Informix.
> Actually i wanted to compare this field with other datetime
> fields in the database.
>
> I am using Informix OWS 7.20. Esql/c 7.23.
It isn't all that easy, but it can be done:
SELECT last_dt,
EXTEND(last_dt - 0 UNITS DAY, YEAR TO SECOND),
last_tm,
(last_tm - DATETIME(0:0:0) HOUR TO SECOND),
EXTEND(last_dt - 0 UNITS DAY, YEAR TO SECOND) +
(last_tm - DATETIME(0:0:0) HOUR TO SECOND)
FROM t;
09/12/1997|1997-12-09 00:00:00|10:27:28|10:27:28|1997-12-09 10:27:28
The types of the output columns are DATE, DATETIME YEAR TO SECOND,
DATETIME HOUR TO SECOND, INTERVAL HOUR(2) TO SECOND, DATETIME YEAR TO
SECOND respectively.
The subtraction in the EXTEND converts the DATE to a DATETIME YEAR TO DAY.
The subtraction in the second term yields an interval. And a DATETIME and
an INTERVAL can be added to yield a DATETIME.
It might be worth writing a simple stored procedure to automate this
conversion...
CREATE PROCEDURE date_time(dt DATE, tm DATETIME HOUR TO SECOND)
RETURNING DATETIME YEAR TO SECOND; RETURN EXTEND(dt - 0 UNITS DAY, YEAR TO SECOND) +
(tm - DATETIME(0:0:0) HOUR TO SECOND);
END PROCEDURE;
I tried reducing the (last_tm - DATETIME(0:0:0) HOUR TO SECOND) term to
(last_tm - DATETIME(0) SECOND TO SECOND) but it didn't work for reasons
which I do not understand (possibly a bug).
Yours,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>