By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,354 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

how to rectify this error

100+
P: 375
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
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
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.