sql query to subtract 2 times

  • I need a simple sql statement with a case to determine whether 2 times need to be added or subtracted

    the case statement i have works... but sql wont allow me to subtract or add the 2 timespan datatypes.

    the case statement reads

    case

    when dbo.Races.rSealed = 0 THEN (dbo.Results.rTime - dboResults.rHandicap)

    else (dbo.Results.rTime + dbo.Results.rHandicap)

    end

    ive been searching everywhere...even guidance on what to read would be appreciated

  • What are the data types and error?

  • ok the statement reads:

    CASE WHEN dbo.Races.rSealed = 0 THEN (dbo.Results.rTime - dbo.Results.rHandicap) ELSE (dbo.Results.rTime + dbo.Results.rHandicap)

    END AS 'Actual Time'

    the data types are both time(4)

    the error is:

    Operand data type time is invalid for subtract operator.

  • Are you sure the Datatype is TIME?

    What version of SQL are you using?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • time datatypes confirmed

    im using sql server 2008 that came bundled with the visual web developer express edition.

    i have pulled the data out of open office (via excel) it's clean & I can programmatically get the result i want but it would be "cleaner" to have the field arrive at the page from a view

    its 3:30 am now for me...so im leaving this for the morning

    appreciate any hints...directions/alternatives to try as its still early days & i can alter the fields to another datatype if it would suit my purposes better?

  • ok cool, sorry .

    You posted in the 2005 section so I got pretty confused he he he

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • just realised...ill repost (or can it be moved?)

  • Michael Artz (12/15/2008)


    ok the statement reads:

    CASE WHEN dbo.Races.rSealed = 0 THEN (dbo.Results.rTime - dbo.Results.rHandicap) ELSE (dbo.Results.rTime + dbo.Results.rHandicap)

    END AS 'Actual Time'

    the data types are both time(4)

    I think that you have to use DATEDIFF and DATEADD functions. So instead of this:dbo.Results.rTime - dbo.Results.rHandicap

    you would have to use this:

    DATEADD(mcs, - DATEDIFF(mcs, dbo.Results.rHandicap, 0), dbo.Results.rTime)

    I haven't tried this yet, so you'll have to test it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply