Date conversion error in SQL Server stored procedure

I have a stored procedure that is throwing the following error when I run it "Msg 241, Level 16, State 1, Procedure sp_Matl_OpenPO_Amt_Adhoc, Line 54
Conversion failed when converting date and/or time from character string." I am using '2012001' for @begdate value and '20120131' for @enddate value when running the stored procedure.

I can't figure out why it is giving me this error.  If I pull out the section of the stored procedure that includes the date reference and run it as a query it runs fine.  Can someone take a look at my procedure and see if anything stands out to you?  

I am using SQL Server 2008.
Who is Participating?
gplanaConnect With a Mentor Commented:
I think you should use "convert" sqlserver function for converting a varchar to datetime or viceversa. Please see this link:
Lee SavidgeCommented:
2012001 isn't a valid date. I assume this is a copy and paste error?
imstac73Author Commented:
Yes sorry.  It should be '20120101'
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Lee SavidgeConnect With a Mentor Commented:
Instead of declaring them as date types, declare them as nvarchar(8) and then cast them as a date in the query.


 not between cast(@begdate as date) and cast(@enddate as date)
imstac73Author Commented:
Do I need to change my conversion on my field?

imstac73Author Commented:
gplana...are you referring to adding a convert to the parameters?
imstac73Author Commented:
I got it working.  What I did was made the parameters data type decimal(8,0) then I used the following conversion in my case statement
CONVERT(char, @begdate, 112) and CONVERT(char, @enddate, 112).

Thanks for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.