2

In my application time info is stored in db in a float field, where:

0 means 0 AM;

0.5 12 AM 

0,999305555555556 is 11:59 PM

The conversion into time information is currently done in my application by Ui components that are designed to work with this way to save time info as float (this was decided before TIME datatype was introduced in SQL Server 2008R2).

My question is:
how to perform this conversion?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
  • 2
    Errm.... Aren't you the one who knows how to convert float to time in your case since your application already does it at UI layer? – Andrey Korneyev Nov 10 '16 at 09:03
  • 1
    Check the UI component code perhaps? – Magnus Nov 10 '16 at 09:03
  • 1
    My app works "as is" since 15 years and it is the first time i need to retrieve with a sql query rime information. Up to now it has been convenient to delegate this task to the Ui layer. Live is made also of simple blocks sometimes. I personally find your comments not useful. Thanks for taking time to read. – UnDiUdin Nov 10 '16 at 09:59
  • 1
    @user193655: All the guys are saying is that as you are the one knowing the formula to get from time to float, you must also be the person to know how to convert it back. Otherwise: how did you ever manage to show a stored time again? So probably you are converting from time to float as 10:30 pm = 22:30 = 22.5 => 22.5 / 24 = 9.375. For the conversion from float to time you'd do the same backwards. It seems however, you haven't even looked up your code and tried anything yourself. – Thorsten Kettner Nov 10 '16 at 10:29

1 Answers1

7

Minute has 60 seconds,
Hour has 3600 seconds,
Day has 86400 seconds,

Multiply your (0-1) float by 86400 (let's call it total_seconds),

1) Floor of division of total_seconds by 3600 will be your hours
2) Subtract hours*3600 from your total_seconds
3) Floor of division of total_seconds by 60 will be your minutes
4) Subtract minutes*60 from your total_seconds
6) What's left will be your seconds

DECLARE @raw_input FLOAT = 0.999305555555556
DECLARE @total_seconds INT
DECLARE @hours INT, @minutes INT, @seconds INT

SET @total_seconds = @raw_input * 86400
SET @hours = FLOOR(@total_seconds/3600)
SET @total_seconds = @total_seconds - @hours * 3600
SET @minutes = FLOOR(@total_seconds/60)
SET @seconds = @total_seconds - @minutes * 60

Edit: or much simpler, adapting a similar question/answer:

DECLARE @raw_input FLOAT = 0.999305555555556
DECLARE @secondss decimal = @raw_input*86400
SELECT  CAST(CONVERT(VARCHAR,DATEADD(SECOND, @secondss, 0),108) AS TIME)

>23:59:00.0000000
user5226582
  • 1,946
  • 1
  • 22
  • 37