Professional Web Applications Themes

Summing time...... - Microsoft SQL / MS SQL Server

Hi I have a field which is a Date/Time field.... It actually only makes use of the Time portion, and this holds a duration of an activity.... However, I want to have a query which returns the total duration of all the times in the records. Is there an easy way, to SUM date/time fields (or just the time elements), to give the total duration of all the records?? Thanks Paul...

  1. #1

    Default Summing time......

    Hi
    I have a field which is a Date/Time field....
    It actually only makes use of the Time portion, and this holds a duration of
    an activity....

    However, I want to have a query which returns the total duration of all the
    times in the records.
    Is there an easy way, to SUM date/time fields (or just the time elements),
    to give the total duration of all the records??

    Thanks


    Paul


    Paul Aspinall Guest

  2. #2

    Default Re: Summing time......

    select sum(dtefld)

    a datetime is just a decimal number with the integer part the date and
    the decimal part the time. As you are just using the time the integer
    part will be 0 (19000101) and you can just sum the fields to give a
    total time.



    Nigel Rivett (SQL Server MVP)
    [url]www.nigelrivett.net[/url]

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Nigel Rivett Guest

  3. #3

    Default Re: Summing time......

    select sum(dtefld)

    a datetime is just a decimal number with the integer part the date and
    the decimal part the time. As you are just using the time the integer
    part will be 0 (19000101) and you can just sum the fields to give a
    total time.

    except that sum will not take a datetime so you can

    select convert(datetime,sum(convert(decimal(18,5),dtefld) ))
    But I suspect that this will lose some precision.

    But maybe better to take advantage of the factr that a date is 2 4 byte
    integer values - the second being the time so

    select
    convert(datetime,convert(binary(8),sum(convert(int ,convert(binary(4),
    dtefld))))) from #a


    Nigel Rivett (SQL Server MVP)
    [url]www.nigelrivett.net[/url]

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Nigel Rivett Guest

  4. #4

    Default Re: Summing time......

    You can use DATEDIFF to SUM time durations to whatever precision you
    require:

    CREATE TABLE Durations (duration DATETIME PRIMARY KEY)

    INSERT INTO Durations VALUES ('1900-01-01T00:00:00.000')
    INSERT INTO Durations VALUES ('1900-01-01T00:10:00.000')
    INSERT INTO Durations VALUES ('1900-01-01T00:11:00.000')
    INSERT INTO Durations VALUES ('1900-01-01T00:12:00.000')
    INSERT INTO Durations VALUES ('1900-01-01T00:30:00.000')

    SELECT SUM(DATEDIFF(SECOND,'19000101',duration)) AS total_duration_seconds,
    DATEADD(SECOND,SUM(DATEDIFF(SECOND,'19000101',dura tion)),'19000101') AS
    total_duration
    FROM Durations

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  5. #5

    Default Re: Summing time......

    Thanks to all replies.....

    Its probably pretty obvious now I see the answer...... Same as anythign I
    suppose... ;-)

    Thanks again


    Paul

    "David Portas" <org> wrote in message
    news:phx.gbl... 
    total_duration_seconds, 


    Paul Guest

Similar Threads

  1. Summing a repeated table...
    By shaoguan in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 27th, 04:05 PM
  2. Summing Variable in CF 4.0
    By DisJohn in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 22nd, 02:44 PM
  3. Summing a column and displaying in the footer
    By William Gower in forum ASP.NET Data Grid Control
    Replies: 5
    Last Post: May 19th, 03:34 PM
  4. Replies: 3
    Last Post: October 6th, 03:10 PM
  5. Summing three levels in ASP
    By Marco Alting in forum ASP
    Replies: 1
    Last Post: September 5th, 07:32 AM

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