Clever Geek Handbook

Firebird distinguishes integer as time or date

I have a table that has the number of seconds as an 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:

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

I am getting the following error:

An 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 how much time I spent figuring it out.

EDIT

I can't believe this is the answer:

Update MyTable
Set TIMESPENT =  time '00:00:00' + ColAmountOfSeconds;
+3


source to share


1 answer


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

You can take advantage of the fact that Firebird supports arithmetic between dates and numbers, 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;
+6


source






More articles:


All Articles