Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Firebird cast integer as Time or Date

I have a table which stores an amount of seconds as integer. I want to display it and use it as Time or Date.

If I write this:

Select Cast(ColAmountofSeconds as Time) as ThisTime From MyTable;

same with:

Select Cast(ColAmountofSeconds as Date) as ThisTime From MyTable;

I get the following error:

Overflow occurred during data type conversion. conversion error from string "14".

Note "14" is the value of the first row in the ColAmountofSeconds column.

This is so natural in SQL Server, that I can't believe the amount of time I've spent on figuring this out.

EDIT

I can't believe this is the answer:

Update MyTable
Set TIMESPENT =  time '00:00:00' + ColAmountOfSeconds;
like image 904
Craig Stevensson Avatar asked Aug 30 '25 17:08

Craig Stevensson


1 Answers

Firebird cast function does not support converting a numeric value to date, time or timestamp.

You can take advantage of the fact that Firebird supports arithmethic between dates and numeric values, so you can write your query like this:

select dateadd(second, ColAmountOfSeconds, cast('00:00:00' as time))
  from myTable;

--or the equivalent:

select cast(cast('2013-01-01' as timestamp) + cast(ColAmountofSeconds as double precision) / 86400 as TIME)
  from myTable;
like image 130
jachguate Avatar answered Sep 02 '25 05:09

jachguate