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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy