Why does this select generate an error

Fatbob73 · #1 (**permalink**) 06-21-10, 15:54

Hi everyone!

Why does the following select work...

Code:

select
	count(distinct l.PID)
from
	LocTrackHist l, Serv s
where
	l.SID = s.SID and
	l.PID = s.PID and
	l.currFacID = 'RVH' and
	l.currLocID like 'M08%' and
	datediff(hh, s.startDtm, getdate()) < 24

but this select fails with the error being "Arithmetic overflow during implicit conversion of VARCHAR value '21/06/2010 3:46:58.116 PM' to a DATETIME field ."

Code:

select
	count(distinct l.PID)
from
	LocTrackHist l, Serv s
where
	l.SID = s.SID and
	l.PID = s.PID and
	l.currFacID = 'RVH' and
	l.currLocID like 'M08%' and
	datediff(hh, s.startDtm, '21/06/2010 3:46:58.116 PM') < 24

reddy_546 · #2 (**permalink**) 06-22-10, 01:14

USE datediff(hh, s.startDtm, '06/21/2010 3:46PM) < 24

I think it will work fine.

First convert both dates to same format.

agrawal.meet · #3 (**permalink**) 06-22-10, 02:00

This could be because of date format. Sybase may be using MM/DD/YYYY format but you are passing date in DD/MM/YYYY format. Since 21 can't be converted to valid month, error is thrown by sybase. Use the solution mentioned by Reddy_546. That should work fine.

pdreyer · #4 (**permalink**) 06-22-10, 08:32

I also suggest you don't use functions on columns in your where clause
using the datediff function on startDtm means that you won't be able to use an index defined on the startDtm column
instead I suggest

Code:

where startDtm>dateadd(hh,-24,convert(datetime,'21/06/2010 3:46:58.116 PM',103))

Fatbob73 · #5 (**permalink**) 06-22-10, 13:35

Thanks for all the suggestions folks!

Internet Services

Web Development

Digital Marketing

Consumer Tech