December 15, 2008 at 8:57 am
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
December 15, 2008 at 9:10 am
What are the data types and error?
December 15, 2008 at 9:16 am
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.
December 15, 2008 at 9:21 am
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]
December 15, 2008 at 9:26 am
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?
December 15, 2008 at 9:28 am
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]
December 15, 2008 at 9:30 am
just realised...ill repost (or can it be moved?)
December 15, 2008 at 9:57 am
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