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.
matlOpenstoredprocedure.sql
imstac73Asked:
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:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
Lee SavidgeCommented:
2012001 isn't a valid date. I assume this is a copy and paste error?
0
 
imstac73Author Commented:
Yes sorry.  It should be '20120101'
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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.

So...

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

replace(CONVERT(date,CONVERT(char,ph.IDTRV),112),'-','')
0
 
imstac73Author Commented:
gplana...are you referring to adding a convert to the parameters?
0
 
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.
0
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.