0

I need to convert datetime to float values before calculation and aggrigation func sum using and i have noticed strange fraction part it gives. Here is the code:

select 
    DATEDIFF_BIG(MILLISECOND, cast(N'2021-01-01 00:00:00.000' as datetime), cast(N'2021-01-01 00:00:05.000' as datetime))/1000.0
  , convert(float, cast(N'2021-01-01 00:00:05.000' as datetime),0)*100000 - convert(float,cast(N'2021-01-01 00:00:00.000' as datetime),0)*100000

gives the following results:

5.000000    5.78703689575195

So where the fraction part comes from (in the second expression)?

okarpov
  • 864
  • 14
  • 22
  • 1
    If you want to aggregate the value, why do you want to use a `float`? A `float` is an inaccurate value, you should expect inaccuracies. Converting a date to a `float` doesn't even make sense. What "floating point value" is today? It's not a number, it's a date... – Thom A Feb 17 '21 at 09:29
  • 2
    Sounds like you're using the wrong datatypes throughout here. It doesn't make sense to aggregate date values (What is yesterday + today?) nor to convert them to floats. Perhaps go back to basics and work out what this data is actually meant to represent and then store it using the appropriate data type. – Damien_The_Unbeliever Feb 17 '21 at 09:33
  • A DATETIME is a specific datatype. Not a number. Casting or converting to a number does not make sense, like the one who tried to convert lead to gold ! So this convert is possible with DATETIME, which is an obsolete datatype and forbidden with DATETIME2 which replace the old DATETIME ! – SQLpro Feb 17 '21 at 09:39
  • Thx you all but the question is about where it comes from – okarpov Feb 17 '21 at 09:45
  • As I mentioned, @okarpov, in my [comment](https://stackoverflow.com/questions/66239041/ms-sql-date-to-float#comment117107274_66239041): *"A `float` is an inaccurate value, you should expect inaccuracies."* If you want to understand how floating point arithmetic works, and why it's not accurate, that isn't a question for [so]. – Thom A Feb 17 '21 at 10:10
  • Though, saying that, there is the question [Why are floating point numbers inaccurate?](https://stackoverflow.com/q/21895756/2029983) which explains. – Thom A Feb 17 '21 at 10:16
  • i checked it with decimal(18,7) almost same result – okarpov Feb 17 '21 at 10:53
  • 1
    You need to read up on precision and scale, and also remember that `datetime` on SQL Server has a dodgy resolution of 3.333...ms so you're going to get rounding errors no matter which data type you try to convert to. – AlwaysLearning Feb 17 '21 at 11:17
  • precision has nothing to do here, if you have whole value 5.0 precision can't add .1234 to the value. precision is about how accurate calculation result is. DATE can be easily presented in long or float for example as number of ticks starting from EPOCH or whatever is used by the system. So all of this is about the calculation of time spent between Start and End dates on the history – okarpov Mar 31 '21 at 09:55

0 Answers0