Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:
CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
set dateformat dmy
go
declare @.startdate as varchar
declare @.enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
GO
Thanx in front
CyaOriginally posted by Wimmo
Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:
CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
set dateformat dmy
go
declare @.startdate as varchar
declare @.enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
GO
Thanx in front
Cya
Simple ... just run this command
sp_helptext cp_cashbox
and you will understand.|||Thanx, i tried what you said but it returns the sp code,
which i edited to
CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
but when i exec it now it returns with the same error as before
the exec command i use is:
exec CP_cashbox @.mID = '10001', @.startdate = '01-01-2000', @.enddate = '01-01-2004'
go
can you tell me what i do wrong?
Thanx
Regards Wim|||Originally posted by Wimmo
Thanx, i tried what you said but it returns the sp code,
which i edited to
CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
but when i exec it now it returns with the same error as before
the exec command i use is:
exec CP_cashbox @.mID = '10001', @.startdate = '01-01-2000', @.enddate = '01-01-2004'
go
can you tell me what i do wrong?
Thanx
Regards Wim
Sorry ... should have seen it first time around
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = @.mID and njdate between @.startdate and @.enddate|||thanx that solves the error,
but the strangest thing happened,
when i exec the sp wit the next code:
exec CP_cashbox @.mID = '10001',@.startdate ='01-01-2000',@.enddate = '01-01-2004'
it returns with a Null value
now when i make a query with the same values it returns the correct value. the query i use is :
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '10001' and njdate between '01-01-2000' and '01-01-2004'
What am i doing wrong
Thanx for your precious time
Greet Wim|||try this :
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = @.mID and convert(varchar,njdate,121) between convert(varchar,@.startdate,121) and convert(varchar,@.enddate,121)|||Originally posted by Enigma
try this :
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = @.mID and convert(varchar,njdate,121) between convert(varchar,@.startdate,121) and convert(varchar,@.enddate,121)
it returns the annoying NULL value again|||Can you paste the DDL of the table ...
CREATE proc CP_Cashbox @.mID varchar(x),@.startdate datetime,@.enddate datetime
where x is the width of the machine_id column in your table|||Originally posted by Enigma
Can you paste the DDL of the table ...
CREATE proc CP_Cashbox @.mID varchar(x),@.startdate datetime,@.enddate datetime
where x is the width of the machine_id column in your table
What is DDL?
the with of the machin_id column is 10|||So .. this should work for you
CREATE proc CP_Cashbox
@.mID varchar(10),
@.startdate datetime,
@.enddate datetime
as
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)|||Thanx alot man i inserted the with of the machine_id column and now it is working. Thanx to your help i really lose some stress today.
Greet Wim:) :) :cool:|||DBA's dont take stress .. they are some of the coolest persons I have seen ... i personally havent seen a dba fuming in public.|||You are completely right, and i am kinda new to this stuf so i am in the proces of becoming on of the coolest!|||Any time you need help you can rely on some of the best dba's i ve known on this site ... especially ... Brett Kaiser, blindman, rnealjr and satya
i myself rely a lot on this site in emergencies|||Originally posted by Enigma
Any time you need help you can rely on some of the best dba's i ve known on this site ... especially ... Brett Kaiser, blindman, rnealjr and satya
i myself rely a lot on this site in emergencies
Thanx for the tip, i think i gonna need some more help in the near future.
