Operand type clash: int is incompatible with void type

Question

Operand type clash: int is incompatible with void type

santoooo

SSCommitted

Points: 1999
More actions
June 16, 2008 at 9:52 am

#70857

Hi All,
I am getting this error when i execting the Stored Procedure,
Server: Msg 206, Level 16, State 2, Procedure , Line 25
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Procedure , Line 25
Operand data type numeric is invalid for modulo operator.
In my Stored Procedure i used modulo operator(%)
SELECT FormCode, DocReceivedDate, PADate, ActualArrivalDateTime,
(TimeDifference/60) + CAST((TimeDifference%60.0 )/100 AS Decimal(9,2)) AS 'Pre-Alert Difference(hh:mm)',
BusinessBranch, BranchName, DeparturePort, DeparturePortName As [Port of Origin],
OriginCountry, OriginCountryName
Here TimeDifference is in minutes,
my Requirement is to show like (12.34) i.e here 12 is hours and 34 is minutes
for example TimeDifference = 123
so i have to show like 2.03 (i.e 2 is hrs and 03 is mins)
to get this i did like above query...
is there any other solution to achieve this (in sql 2000)
it is working in SQL 2005 but the same sp when i am trying to execute in SQL 2000 it is give the above error..
please help me in the issue...
thanks in advance.
- Santosh

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

santosh_devaki (6/16/2008)
Server: Msg 8117, Level 16, State 1, Procedure , Line 25
Operand data type numeric is invalid for modulo operator.

2005 supports int, money & numeric data types for modulo, however, SQL 2000 only supports int data types for this operator.

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

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736632

June 16, 2008 at 10:45 am

#829339

So, time%60 should work, correct?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Steve Jones - Editor (6/16/2008)
So, time%60 should work, correct?

the modulo would work, yes. But in order to perform the division and end up with a numeric or decimal value, he'd have to do a *1.0 as well.

so:

...

CAST((TimeDifference%60)*1.0/100 AS Decimal(9,2))

...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

santoooo SSCommitted Points: 1999 More actions · Answer 4

Yes, it has to work, but it is not working... in my prod db

it is working in the my local SQL 2000 also.

i dont y it is not working in my prod db ..

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

So what IS the data type of TimeDifference? When you said is was "in minutes" I was thinking you mean it was INT - is that not the case?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Steve Jones - Editor (6/16/2008)
So, time%60 should work, correct?

If "time" is an int also.

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

santoooo SSCommitted Points: 1999 More actions · Answer 7

Actually i am not storing the resultant query in the any table.

I am directly running the query to generate the report.

------------

Finally i got the solution for this,

I achieved the requirement as follows

SELECT FormCode, DocReceivedDate, PADate, ActualArrivalDateTime,

CONVERT(CHAR(8), DATEADD(MINUTE, TimeDifference % 1440, '00:00'), 108) AS 'Pre-Alert Difference(hh:mm)',

BusinessBranch, BranchName, DeparturePort, DeparturePortName As [Port of Origin],

OriginCountry, OriginCountryName

CONVERT(CHAR(8), DATEADD(MINUTE, TimeDifference % 1440, '00:00'), 108)

Here i am converting the total minutes into time format (HH:MM) and finally converted to String.

Thanks for your good and quick reply(s).

Santosh

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Glad it worked out for you.

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