469,307 Members | 2,597 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

how to rectify this error

375 256MB
Hi,

I want to retrieve the values from store_geofence table where the timeduration exceeds certain interval

the procedure is as below
Expand|Select|Wrap|Line Numbers
  1. CREATE
  2.  procedure [dbo].[GeofenceByCustomer]
  3. @regno nvarchar(50),
  4. @frmdate nvarchar(50),
  5. @todate nvarchar(50),
  6. @Geofence nvarchar(50),
  7. @interval int,
  8. @userid nvarchar(50)
  9. as
  10. begin
  11. declare @val int
  12. Declare @strSql as nvarchar(3000)
  13. Select @val=count(*) from basestation where superuserid=@userid and
  14. base_station_name=@Geofence
  15.  
  16. --if(@val=0)
  17. begin
  18.         create table #templ(registrationno nvarchar(50),basestation
  19. nvarchar(50),entry_time datetime,exit_time datetime,duration
  20. int)
  21.         select @strSql='
  22.         insert into #templ(registrationno,basestation,entry_time,exit_time,duration)
  23.         select
  24. registrationno,basestation,entry_time,exit_time,datediff(mi,entry_time,exit_time)
  25. as duration from store_geofence where  entry_time>'+@frmdate+' and
  26. exit_time<'+@todate+' and datediff(mi,entry_time,exit_time)>'+@interval+'
  27. and basestation in ('+@Geofence+')  order by entry_time,registrationno'
  28.         execute (@strSql)
  29.         print @strSql
  30.         select * from #templ
  31.         drop table #templ
  32. end
  33.  
  34. end
  35. GO
  36.  
I should specify the duration in int to take affect

datediff(mi,entry_time,exit_time)>'+@interval+'


but i do not know how to rewrite this path
i cannot put as
datediff(mi,entry_time,exit_time)>'+@interval+'


it returns an error
Syntax error converting the nvarchar value '
insert into #templ(registrationno,basestation,entry_time,exit_ time,duration)
select
registrationno,basestation,entry_time,exit_time,da tediff(mi,entry_time,exit_time)
as duration from store_geofence where entry_time>01/01/2008 and
exit_time<01/01/2008 and datediff(mi,entry_time,exit_time)>' to a column of data type int.

How should i rewrite it

please help

regards
cmrhema
Apr 13 '08 #1
1 1073
Delerna
1,134 Expert 1GB
I think the error is actually comming from the date comparison.
There should be singe quotes around them
like this

insert into #templ(registrationno,basestation,entry_time,exit_ time,duration)
select
registrationno,basestation,entry_time,exit_time,da tediff(mi,entry_time,exit_time)
as duration from store_geofence where entry_time>'2008-01-01' and
exit_time<'2008-01-01' and datediff(mi,entry_time,exit_time)> etc

As they are i think they are being read as 1 divided by 1 divided by 2008
when the query sting is executed.



Note, there won't be any entry_time that is both less than and greater than 2008-01-01
Apr 13 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Jason Clark | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.