Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Lightbulb Unanswered: 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
    Cya

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: error 241: Syntax error converting datetime from character string

    Originally 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
    Code:
     sp_helptext cp_cashbox
    and you will understand.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    re

    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

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: re

    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

    Code:
    select sum(moneyout) / sum(moneyin)*100 as cashbox 
    from dbo.total 
    where machineID = @mID and njdate between @startdate and @enddate
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    re

    thanx that solves the error,
    but the strangest thing happened,
    when i exec the sp wit the next code:

    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 :

    Code:
    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

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    try this :

    Code:
    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)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Originally posted by Enigma
    try this :

    Code:
    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

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    re

    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

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    So .. this should work for you
    Code:
    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)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    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

  12. #12
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    DBA's dont take stress .. they are some of the coolest persons I have seen ... i personally havent seen a dba fuming in public.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  13. #13
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    You are completely right, and i am kinda new to this stuf so i am in the proces of becoming on of the coolest!

  14. #14
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  15. #15
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •