![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Thu, Oct 26 2023 1:00 AM | Permanent Link |
Ian Branch | 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 |
Thu, Oct 26 2023 2:57 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, Oct 26 2023 3:30 PM | Permanent Link |
Terry Swiers | > 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. |
Thu, Oct 26 2023 4:47 PM | Permanent Link |
Ian Branch | 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 |
Fri, Oct 27 2023 1:48 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Terry
Brilliant. I never thought of cast(nnn as INTERVAL DAY) I was hung up on the need for a string constant ![]() Roy Lambert |
Fri, Oct 27 2023 11:35 AM | Permanent Link |
Terry Swiers | > 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. |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |