Invalid operator for data type. Operator equals di

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Invalid operator for data type. Operator equals di

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-27 : 10:17:01

Hello,
I 've been trying to select from sql server but it generates this error (below), it works in Access but not work in sql server.

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals divide, type equals datetime.

select (((inc.inc_to-inc.inc_from)-
abs(ref_date.ref_begin_date-inc.inc_from))+
((ref_date.ref_end_date-ref_date.ref_beg_date)-
abs(ref_date.ref_end_date-inc.inc_to)))/2 AS tdays
from .....
where ...

How do I modify this to make it work.

Any help is appricated.
Jenny.

There is no stupid question.
www.single123.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-12-27 : 10:22:29

why use abs with dates?
use dateadd and datediff functions

Go with the flow & have fun! Else fight the flow

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-27 : 10:58:45

That is in access.
Can you help me to modify it?
Thanks,
Jenny.

There is no stupid question.
www.single123.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-12-27 : 11:00:26

how about you tell us what you're trying to do??

Go with the flow & have fun! Else fight the flow

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-27 : 13:27:27

I am trying to take
1. inc_to SUBTRACT inc_from
2. ref_begin_date SUBTRACT inc_from
3. ref_end_date SUBTRACT ref_beg_date
4. ref_end_date SUBTRACT inc_to
5. ((1 - 2) + (3 - 4)) DIVIDE by 2
I do not know why they used abs in ms Access, does that make any difference?
They are all DATETIME fields....
Thanks.

select (((inc.inc_to-inc.inc_from)-
abs(ref_date.ref_begin_date-inc.inc_from))+
((ref_date.ref_end_date-ref_date.ref_beg_date)-
abs(ref_date.ref_end_date-inc.inc_to)))/2 AS tdays

There is no stupid question.
www.single123.com

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-27 : 23:08:36

try this..
select
(( datediff(day,inc.inc_to,inc.inc_from) - datediff(day,ref_date.ref_begin_date,inc.inc_from) )
+
( datediff(day,ref_date.ref_end_date,ref_date.ref_beg_date) -
datediff (day,ref_date.ref_end_date,inc.inc_to)
))/2

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-28 : 09:12:58

I got the error for divide by zero, so I put all IsNull function, but the error still occured.

(829 row(s) affected)

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

select
(( datediff(day, IsNull(inc.inc_to, 0), IsNull(inc.inc_from, 0)) -
datediff(day, IsNull(ref_date.ref_begin_date, 0), IsNull(inc.inc_from, 0)) )
+
( datediff(day, IsNull(ref_date.ref_end_date, 0), IsNull(ref_date.ref_beg_date, 0)) -
datediff (day, IsNull(ref_date.ref_end_date, 0), IsNull(inc.inc_to, 0))
))/2

There is no stupid question.
www.single123.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-28 : 09:24:30

Since the only <devideBy> operator in the statement above is "/2" I think the error can't be happening in this statement. Is there more code to this?

devide by zero errors only happen if the denominator is 0.
select 0/2 (no error)
select 2/0 (error)

>>There is no stupid question
How about, "Excuse me, is my head on fire?"

EDIT:
or "Miss, where did you get that lovely maternity dress?...Oh, you're not?"

Be One with the Optimizer
TG

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-28 : 09:44:19

This is the stmt above....
Do you think this is wrong?
Thanks for your help.
Jenny.

(IsNull(Inc.la_money_Inc,0)+
IsNull(Inc.tra_money_Inc,0)+
IsNull(Inc.od_inc,0))/datediff(day,IsNull(Inc.Inc_to,1), IsNull(Inc.Inc_from,1)) AS average,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-28 : 10:06:45

Yes, this one could cause a devide by zero error.

>>datediff(day,IsNull(Inc.Inc_to,1), IsNull(Inc.Inc_from,1))
this could result in 0

select datediff(day,IsNull( cast(null as datetime) ,1), IsNull( cast(null as datetime) ,1))

Let me see if I can figure out what your big picture is and possibly suggest an alternative...

Be One with the Optimizer
TG

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-28 : 10:17:56

Actually, this is the correct one. But it errors.
datediff(day,IsNull(Inc.Inc_to,0), IsNull(Inc.Inc_from,0))

I do not understand this:
select datediff(day,IsNull( cast(null as datetime) ,1), IsNull( cast(null as datetime) ,1))

thanks,
Jenny.

There is no stupid question.
www.single123.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-28 : 10:25:31

>>I do not understand this:
I was just showing your statement could generate a 0. In fact if Inc_from and Inc_to are the same day then it will result in "0".

Ok, I'm going to assume that [average] is the daily average of the sum of la_money, tra_money, and od_inc.
I dont know why your datediff function seems to reverse the from and to dates though. Doesn't that result in a negative number? That maybe why you used the ABS function before?

Anyway this would not generate errors and produce a NULL value if either Inc_from or Inc_to is null (or they are the same day):


select average = 
		(IsNull(Inc.la_money_Inc,0)+
		IsNull(Inc.tra_money_Inc,0)+
		IsNull(Inc.od_inc,0))
		/nullif(datediff(day,Inc.Inc_to, Inc.Inc_from),0)

Be One with the Optimizer
TG

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-28 : 10:40:43

It worked. Woh, big thanks.
Jenny.

There is no stupid question.
www.single123.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources