error subtracting 2 time datatypes

Question

error subtracting 2 time datatypes

Michael Artz

SSChasing Mays

Points: 615
More actions
December 15, 2008 at 9:37 am

#216586

I need a simple sql statement with a case to determine whether 2 times need to be added or subtracted (I amunsure of the correct forum to post this)
the case statement i have works... but sql wont allow me to subtract or add the 2 timespan datatypes.
the case statement reads
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.
ive been searching everywhere...even guidance on what to read would be appreciated

Viewing 13 posts - 1 through 12 (of 12 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

This is a longshot Michael and I apologise if "sucking eggs" comes to mind...does 2008 have timediff / timeadd functions?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I don't have SQL 2008 to test on, but I assume it applies, you should be using the Date functions, DateAdd, DataDiff, etc...

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Just noticed, this is also double posted. It is also posted in a SQL Server Experss 2005 forum. What version of SQL Server are you using? And please don't double post.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

It's also posted under TSQL2k5

----------------------------------------------
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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Christopher Stobbs (12/15/2008)
It's also posted under TSQL2k5

Lynn Pettis (12/15/2008
Just noticed, this is also double posted. It is also posted in a SQL Server Experss 2005 forum. What version of SQL Server are you using? And please don't double post.

I just linked this thread on the one that Lynn mentioned.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Michael Artz SSChasing Mays Points: 615 More actions · Answer 6

OK i've read how to post correctly... but .... well im a newby, so im afraid i use the UI to create tables... ill post definitions if that helps?

and the full statement.

The 2 tables are:

Races:

rDate Date

rSealed bit

rDescription nvarchar(50)

rLocked bit

Results:

MemberID int

rPercentile int

rGrpID int

rCurrentMember bit

rChampPoint int

rIgnoreResult bit

rTime time(4)

rHandicap time(4)

Results Table Data

SELECT '997','971','104','0','33','1','5','0','02:43:24.0000','00:35:00.0000' UNION ALL

SELECT '995','839','104','0','38','1','5','0','02:33:58.0000','00:39:33.0000' UNION ALL

SELECT '996','827','104','0','49','1','5','0','02:41:00.0000','00:49:00.0000' UNION ALL

SELECT '992','802','104','0','37','1','5','0','02:19:40.0000','00:38:30.0000' UNION ALL

SELECT '993','16','104','0','48','1','5','0','02:21:29.0000','00:48:18.0000' UNION ALL

SELECT '994','454','104','0','29','1','5','0','02:24:09.0000','00:29:45.0000'

Races Table Data:

SELECT '102','2008-08-02','MBO Accountants'' Handicap','0','0' UNION ALL

SELECT '103','2008-08-17','Star of the West','0','0' UNION ALL

SELECT '104','2008-09-07','Artz & Kay Half Marathon','1','0'

OK..... so hopefully thats enough for you to see that what i want to do is subtract (the last row of data as example) 02:24:09.0000 - 00:29:45.0000 in the case that Races.rSealed is '1' & add them in the case that Races.rSealed is '0'

since they are times & not dates I thought it should be able to simply add or subtract (which is what i would do in VB) so i had

SELECT dbo.Results.RESULTID, dbo.Results.rHandicap, dbo.Results.rTime,

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

END AS 'Actual Time'

from results etc

hope this helps answer my question...ill now try the 2 suggestions offered thus far

Michael

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

You can use the UI to create the table create scripts for you. Right click on the table and go from there. Remember, we need to be able to CREATE the tables in our own environments.

Michael Artz SSChasing Mays Points: 615 More actions · Answer 8

OK seems I'm more of a newb than anyone realises (even me)

the "UI" i was referring to was within visual web developer ..... i havent found any other means to view databases from the links installed on my vista installation (yeah i know...Vista)

there are no (immediately obvious) links to show create table definistions from right clicking within the data explorer of web developer. There is a properties page which is what use to get the definitons i posted

Michael Artz SSChasing Mays Points: 615 More actions · Answer 9

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.
-- RBarryYoung, (302)375-0451 visit my blog: http://www.MovingSQL.com
Proactive Performance Solutions, Inc. "Performance is our middle name."

OK tried that. but i got an error "datediff resulted in an overflow. The number of dateparts seperating two date/time instances is too large. Try o use datediff with a less precise datepart.

I'm guessing i need some kind of cast to a date since this really is a time datatype??

but my miniscule brain is about to explode.

I should have saved them as datetimes & been done with it...I was lured by the sexy time datatype which seemed to suit my purposes better!!

growl mumble...wheres the coffee

####

OK...just looked at that sql statement...seems despite that error I got a column with the times added corectly ..... any clues on how to avoid that error? or will it resolve itsself if i add that sql to a view?

more experimenting

######

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Michael Artz (12/15/2008)
OK seems I'm more of a newb than anyone realises (even me)
the "UI" i was referring to was within visual web developer ..... i havent found any other means to view databases from the links installed on my vista installation (yeah i know...Vista)
there are no (immediately obvious) links to show create table definistions from right clicking within the data explorer of web developer. There is a properties page which is what use to get the definitons i posted

I'm sorry, but when you were talking about the UI, I thought you were talking about SQL Server Management Studio (SSMS, or MS for short).

Michael Artz SSChasing Mays Points: 615 More actions · Answer 11

Seems the error i was getting in the last sql statement was an overflow... it was looking at miliseconds difference & consequently soon ran over the limit. So Im about tochange that to only look at seconds and see if i have any more luck.

Thanks to all

Michael

Michael Artz SSChasing Mays Points: 615 More actions · Answer 12

OK here is the SQL...it's workin as intended although i still want to tweak it a little:

SELECT

dbo.Results.rFastest, dbo.Results.rPercentile, dbo.Results.rGrpID, dbo.Results.rCurrentMember, dbo.Results.rChampPoint, dbo.Results.rIgnoreResult,

dbo.Results.RaceID, dbo.Results.ResultID,

CASE WHEN dbo.Races.rSealed = 'True' THEN

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

ELSE

DATEADD(s, DATEDIFF(s, dbo.Results.rHandicap, 0), dbo.Results.rTime)

END

AS [Actual Time], dbo.Results.rTime, dbo.Results.rHandicap, dbo.Races.rSealed

FROM dbo.Results INNER JOIN

dbo.Races ON dbo.Results.RaceID = dbo.Races.RACEID

Idealy i would want to return 2 columns of data according to the following rules

its a little weird in that the recorded time (rTime) is always the "watch time of the finishing runner. And we run 2 formats of running events. One with a stagered start according to handicap (so handicap time must be deducted from rTime to get the runner's actual time. (Actual time = rTime - rHandicap)

The other is a blanket start (or sealed handicap) where again rTime represents the "watch time" of the runner, (in this case the "Actual time" of the runner) and a calculated time must be made to decide on the handicap winner. "Handicap Time" = (rTime(In this case Actual) + rHandicap)

so my naming convention is a little weird. If there is a way to alter the name of the returned field (i guess "ActualTime" included in the where statement??

I am unsure how to acheive this something like??

case WHEN dbo.Races.rSealed='True' THEN

ActualTime = rTime

Handicapped Time =DATEADD(s, - DATEDIFF(s, dbo.Results.rHandicap, 0), dbo.Results.rTime)

Else

ActualTime = DATEADD(s, DATEDIFF(s, dbo.Results.rHandicap, 0), dbo.Results.rTime)

Handicapped Time = rTime

any guidance on how to achieve this would be appreciated as this is way outside my skill level, but would make the VB programming so much simpler.

thank you in anticipation

Michael