Hi Team,
I have date times stored as floats in a column.  They look like this  42789.340149294.
How do I convert them to an actual datetime/timestamp?

Regards & TIA,
Ian

Ian


It could be done using intervals and building an sql statement for each row in the table but, especially if its a one off you'd be better using Delphi

This is one idea

select
CAST('1899-12-30 0:0:0.0' AS TIMESTAMP) + INTERVAL '42789' DAY ,
CAST('1899-12-30 0:0:0.0' AS TIMESTAMP)
FROM JobTickets

BUT it would mean building sql for each row in the table and calculating the constant for the hours, minutes and seconds is a bit nasty.

I could (probably) write sql to create a string of the timestamp which would then be castable to a timestamp  but it would be horrible


Roy Lambert

> I have date times stored as floats in a column.  They look like this  42789.340149294. How do I convert them to an actual datetime/timestamp?

CREATE FUNCTION "FloatToTS" (IN "FloatDate" FLOAT)
RETURNS TIMESTAMP
BEGIN
DECLARE aTS TIMESTAMP default NULL;
DECLARE DayPortion INTEGER default 0;
DECLARE DecPortion INTEGER default 0;

BEGIN
SET DayPortion = Trunc(FloatDate);
SET DecPortion = Trunc((FloatDate - DayPortion) * 86400000);
SET aTS = DATE '1899-12-30' + cast(DayPortion as INTERVAL DAY) + cast(DecPortion as INTERVAL MSECOND);
EXCEPTION
END;

RETURN aTS;
END


Once you have the function, you can either return the float as a timestamp in a query.   You can also rework the function into a single SQL statement and create a computed field on the table.

Terry Swiers wrote:
  You can also rework the function into a single SQL statement and create a computed field on the table.

Tks Terry.  That works fine.
How would I work that into a computed field pls?

Regards & TIA,
Ian

Terry


Brilliant. I never thought of cast(nnn as INTERVAL DAY) I was hung up on the need for a string constant

Roy Lambert

> How would I work that into a computed field pls?

Rework the actual date caclulation and replace DayPortion and DecPortion with the full calculations that were done above it.