error 241: Syntax error converting datetime from character string

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.