Hello -
I've created a stored procedure that works fine. However, when I call it I get the following error message:
Msg 8114, Level 16, State 5, Procedure StdPrc_PL, Line 0
Error converting data type varchar to date.
What does that mean and how can I correct it?
Thank you,
John
That error message says that some place in your stored procedure, you are trying to convert a string (varchar) to a date, and that that conversion is failing. This can happen either because you are trying to convert a wrong column or wrong data - for example, trying to convert the string "foo" to a date, or it can happen if you are trying to convert a string that is incompatible with the locale setting you are using. For example, if you are using the US system (en-US) where dates are in mm/dd/yyyy format, and you tried to convert a date in British format (dd/mm/yyyy), for example, 21/05/2015, this can happen.
You have to debug the code to see where the problem is. If you are able to post the code to the forum, people might be able to offfer suggestions as to where to look to narrow down the problem.
HMMMMM, thanks for your reply JamesK!
The interesting thing is that when I take the code out of the SP and just run a query it works just fine?
John 
Assuming the environments are the same, the only explanation I have is that the inputs must somehow be different.
Are there any "SET DATEFORMAT" statements in the stored proc that you might perhaps be omitting when you run it outside of the stored proc?
A colleague just suggested to me that the error is caused by the fact that I am joining on dates. He said try using CAST to convert them to a varchar for the comparison. Ill give that a shot and see what happens? I am not too familiar with CAST but Ill figure it out!!
Thanks again,
John
Yes, that is the usual place where people run into problems with date conversions. Also, if you have some where clauses that compare a date column against a string column.
Once you identify potential trouble spots, you can run some simplified queries to identify the offending rows. For example, if you have a column named strDate that you are casting/converting to date/datetime in a where clause or join condition, you can try something like this to identify where the problem might be:
SELECT * FROM Tbl WHERE ISDATE(strDate) = 0;
SELECT CAST(strDate) as DATETIME FROM Tbl;
James -
It did not work as expected. Would you happen to know if you have to do the cast in the joins only? Can you use the variable in other areas? Also, do you know why the same code would run and give correct results with no conversions in a query?
Thanks,
John
The most likely reason it is working in a query is because it is using a different plan - and that plan ends up skipping the offending rows.
To further help we really need to see the code and possibly sample data to reproduce the issue.
As a general rule you should not be casting/converting a datetime column to varchar for comparisons. This type of errors is normally seen when there is a character column where date strings are stored - and trying to compare that column to an actual datetime column or variable. Data type precedence will implicitly convert varchar values to datetime if one of the values being compared is defined as a datetime.
You may get errors although you use CAST or CONVERT function. What is the data type of date columns?
They are date format, madhivanan
John
Madhivanan was asking about the datatype, rather than the format. You can find the data type if you open up the object explorer in SSMS and navigate to the node with the name of the table and expand it to see the columns. You can also use a query to find it like this
SELECT TYPE_NAME(system_type_id)
FROM sys.columns
WHERE name = 'YourColumNameHere'
AND OBJECT_NAME(object_id) = 'YourTableNameHere'
If you can post at least parts of the query where date/datetime columns are involved, that would make it easier for someone to suggest possible issues.
yes, the data type is date.....