Ian Boyd wrote:
> Consider:
>
> CREATE TABLE #foo (StartDateTime datetime, EndDateTime datetime)
> INSERT INTO #foo (StartDateTime, EndDateTime) VALUES ('2006-06-26
> 15:54:03.000', '2006-06-26 15:58:33.000')
>
> SELECT
> StartDateTime,
> EndDateTime,
> DATEDIFF(minute, StartDateTime, EndDateTime) AS MinutesDiff,
> CAST((EndDateTime - StartDateTime) AS double precision)*24.0*60.0 AS
> DaysToMinutes,
>
> DATEDIFF(second, StartDateTime, EndDateTime) AS SecondsDiff,
> CAST((EndDateTime - StartDateTime) AS double precision)*24.0*60.0*60.0 AS
> DaysToSeconds
> FROM #foo
>
> DROP TABLE #foo
>
>
> The results of the select are:
>
> StartDateTime: 2006-06-26 15:54:03.000
> EndDateTime: 2006-06-26 15:58:33.000
>
> Minutes Diff: 4
> DaysToMinutes: 4.5000000000000009 <===0.5 too high
>
> SecondsDiff: 270
> DaysToSeconds: 270.00000000000006 <=== correct to 1E-13
>
>
> How is it that the same math that results in seconds being correct to the
> 13th decimal place makes the minutes off in the first decimal place? It
> can't be the math engine and rounding errors. It must be something like leap
> minutes now in June or something. But even so, the math is unaffected by
> that.
>
> So what's going on?

"Ian Boyd" <ian.msnews010@avatopia.com> wrote in message
news:%23Ho0UI3oGHA.5104@TK2MSFTNGP04.phx.gbl...
>i figured it out.
>
> DATEDIFF(minute) doesn't return the number of minutes between two dates,
> it returns "the number of date and time boundaries crossed between two
> specified dates"
>
> It returns whole numbers, when i assumed a fractional result.
>
> e.g.
>
> DECLARE @d1 datetime
> DECLARE @d2 datetime
> SET @d1 = '2006-07-04'
> SET @d2 = '2006-07-04 6:30:00.000'
> SELECT @d1, @d2
>
> --Number of hours between midnight and 6:30am : 6.5
> --Difference between midnight and 6:30am in hours: 6
>
> --Number of hour units crossed by midnight and 6:30AM
> SELECT DATEDIFF(hour, @d1, @d2) --6
>
> --Number of hours between midnight and 6:30AM
> SELECT CAST(@d2-@d1 AS double precision)*24 --6.5
>
>

> What is "double precision"? Not a SQL Server datatype.

Ian Boyd wrote:
>> What is "double precision"? Not a SQL Server datatype.
>
> From BOL:
> The Microsoft® SQL ServerT float[(n)] data type conforms to the SQL-92
> standard for all values of n from 1 to 53.
> The synonym for double precision is float(53).
>
>

> StartDateTime: 2006-06-26 15:54:03.000
> EndDateTime: 2006-06-26 15:58:33.000
>
> Minutes Diff: 4
> DaysToMinutes: 4.5000000000000009 <===0.5 too high
>

> This is true for any date part, and can be very confusing until you figure
> it out.
> For example, the difference in years between Jan 1, 2006 and Dec 31, 2006
> is 0 (no year boundary crossed)
> but the difference in years between Dec 31, 2006 and Jan 1, 2007 is 1.