Professional Web Applications Themes

Using DateDiff to get time duration - Microsoft SQL / MS SQL Server

To get the time duration of downtime, I have 2 fields called STARTDATETIME and ENDDATETIME. Both are datetime type. I've been using FIGURE 1 statement to get the value in decimal of a hour, but I get incorrect totals when summing the downtime values because as you can see in FIGURE 2, some downtime totals are in scientific format. Also, but not as important, is that using FLOAT(8) type causes SQL to round off downtime to cause not exactly precise time durations. Any ideas on getting around the summing problem and/or precision of time duration? FIGURE 1 CAST(DATEDIFF(s, StartDateTime, EndDateTime) ...

  1. #1

    Default Using DateDiff to get time duration

    To get the time duration of downtime, I have 2 fields called STARTDATETIME
    and ENDDATETIME. Both are datetime type. I've been using FIGURE 1 statement
    to get the value in decimal of a hour, but I get incorrect totals when
    summing the downtime values because as you can see in FIGURE 2, some
    downtime totals are in scientific format.

    Also, but not as important, is that using FLOAT(8) type causes SQL to round
    off downtime to cause not exactly precise time durations.

    Any ideas on getting around the summing problem and/or precision of time
    duration?

    FIGURE 1

    CAST(DATEDIFF(s, StartDateTime, EndDateTime) AS float(8)) / 3600 AS Downtime



    FIGURE 2

    STARTDATETIME ENDDATETIME DOWNTIME
    ---------------------------------------------------------------------------
    ---
    8/8/2003 11:27:29 AM 8/8/2003 11:28:50 AM 0.0225
    8/8/2003 10:58:14 AM 8/8/2003 11:05:53 AM 0.1275
    8/8/2003 7:54:22 AM 8/8/2003 7:56:30 AM
    3.55555555555556E-02
    8/8/2003 7:13:26 AM 8/8/2003 7:13:59 AM
    9.16666666666667E-03


    Scott Guest

  2. #2

    Default Re: Using DateDiff to get time duration

    Scott,

    There are two issues here.

    First, if your times are more precise than to the second, datediff will
    be inaccurate, since it returns the number of "seconds boundaries"
    between values. So two values just 3 milliseconds apart but right at
    the "turn of a second" could have datediff(s,...) of 1, while a
    different two values 997 milliseconds apart, but not straddling an exact
    second moment could have datediff(s,...) of zero. You can be somewhat
    more precise with cast(datediff(ms,...) as float)/3600000, though SQL
    Server datetime values have a milliseconds value ending in 0, 3, or 7 -
    being accurate only to about 1/300 of a second.

    Also (a less likely cause, I think), you may be thinking that FLOAT(8)
    means 8-byte floating point, but in fact, FLOAT(8) is the same as REAL,
    good-old single precision floating point. The SQL standard supports a
    parameter to FLOAT that indicates a minimum number of binary digits for
    the mantissa. SQL Server translates FLOAT(n) for n up to and including
    24 to REAL, and FLOAT(n) for n between 25 and 53 to FLOAT.

    See the Books Online article "float and real" for details, or just see
    if using float, not float(8) helps any.

    -- Steve Kass
    -- Drew University
    -- Ref: 747EBB5C-6AB5-4F9C-865D-667487C1B330

    Scott wrote:
     

    Steve Guest

  3. #3

    Default Re: Using DateDiff to get time duration

    Hi Scott,

    I think you're getting caught up with datediff by specifying seconds. Why
    not just subtract the dates i.e.

    select cast(StartDateTime, EndDateTime as float)

    Regards,

    Rory.

    "Scott" <com> wrote in message
    news:phx.gbl... 
    statement 
    round 
    Downtime 
    -- 


    Rory Guest

  4. #4

    Default Re: Using DateDiff to get time duration

    oops

    select cast( EndDateTime - StartDateTime as float)

    "Rory O'Connell" <com.au_NOSPAM> wrote in message
    news:.. [/ref]
    STARTDATETIME 
    > statement 
    > round 
    > Downtime 
    >[/ref]
    ------------------------------------------------------------------------- 
    >
    >[/ref]


    Rory Guest

Similar Threads

  1. Datediff() bug
    By OldCFer in forum Macromedia ColdFusion
    Replies: 21
    Last Post: November 24th, 09:35 AM
  2. FLV returning different "time" and "duration"
    By Happiest Camper in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: March 10th, 01:28 AM
  3. Datediff
    By Alexander Hoffmann in forum PHP Development
    Replies: 1
    Last Post: December 11th, 08:34 PM
  4. Formatting time as a duration?
    By Kris Rudin in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: November 13th, 06:21 AM
  5. web photo gallery slide show time duration change-photoshop 7
    By sunny in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 0
    Last Post: July 13th, 03:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139