skinsfan99
asked on
MS Stored Procedure - Conversion failed when converting datetime from character string.
I have a stored procedure that is giving me the error 'Conversion failed when converting datetime from character string.' If I run this code in QA there is no issues and the recordset returns as expected. Here is the section of code in question:
IF @FROMDATE IS NOT NULL
BEGIN
SET @WHERE = ' WHERE CONVERT(VARCHAR(10), L.[insertDT], 101) >= ''' + @FROMDATE + ''
END
IF @TODATE IS NOT NULL
BEGIN
IF @WHERE IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND CONVERT(VARCHAR(10), L.[insertDT], 101) <= ''' + @TODATE + ''
END
ELSE
BEGIN
SET @WHERE = ' WHERE CONVERT(VARCHAR(10), L.[insertDT], 101) <= ''' + @TODATE + ''
END
END
Here is the call to the stored procedure:
exec storeprocedure '08/02/2013', '08/08/2013', 10370, 0, 0, -1
@FROMDATE AND @TODATE declared as DATETIME.
What am I missing??
Thanks,
David
IF @FROMDATE IS NOT NULL
BEGIN
SET @WHERE = ' WHERE CONVERT(VARCHAR(10), L.[insertDT], 101) >= ''' + @FROMDATE + ''
END
IF @TODATE IS NOT NULL
BEGIN
IF @WHERE IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND CONVERT(VARCHAR(10), L.[insertDT], 101) <= ''' + @TODATE + ''
END
ELSE
BEGIN
SET @WHERE = ' WHERE CONVERT(VARCHAR(10), L.[insertDT], 101) <= ''' + @TODATE + ''
END
END
Here is the call to the stored procedure:
exec storeprocedure '08/02/2013', '08/08/2013', 10370, 0, 0, -1
@FROMDATE AND @TODATE declared as DATETIME.
What am I missing??
Thanks,
David
ASKER
Here are the lines showing how @FROMDATE AND @TODATE are set:
@FROMDATE DATETIME,
@TODATE DATETIME
I tried you suggestion adding the SELECT @WHERE after each SET @WHERE line but I did npt get anyting displayed other than the error message.
Any other ideas??
Thanks,
David
@FROMDATE DATETIME,
@TODATE DATETIME
I tried you suggestion adding the SELECT @WHERE after each SET @WHERE line but I did npt get anyting displayed other than the error message.
Any other ideas??
Thanks,
David
>but I did npt get anyting displayed other than the error message.
First thing that comes to mind is that the error is occuring before this block.
Can you post the entire SP T-SQL?
First thing that comes to mind is that the error is occuring before this block.
Can you post the entire SP T-SQL?
ASKER
OK Jim, I have attached the SP. Thanks for your help!!
ASKER
Guess it would help to actually attach the file....
SP-Code.txt
SP-Code.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
While your solution does not error, it also does not produce SQL that works.
The whole thing that does not make sense to me is that my SQL works when I run it in QA. It only becomes an issue when I move it to the SP and make it dynamic. I have been coding date comparisons like this for a long time and I have never run into this issue before.
jimhorn, have you had any further thoughts after reviewing my SP file??
Thanks
The whole thing that does not make sense to me is that my SQL works when I run it in QA. It only becomes an issue when I move it to the SP and make it dynamic. I have been coding date comparisons like this for a long time and I have never run into this issue before.
jimhorn, have you had any further thoughts after reviewing my SP file??
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry @skinsfan99.. You did not specify really anything about the SPROC datatype for [insertDT] and how you used the @WHERE clause. I put together what I had found..
I do also think this may play into the picture as well. Yes, I know you use IS NULL, but if I am reading the TN Article correctly the ISNULL is used for the CHAR(10)..
http://technet.microsoft.com/en-us/library/ms184325.aspx
C. Testing for NULL in a WHERE clause
Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL in the weight column. Note the space between IS and NULL.
HTH,
Kent
I do also think this may play into the picture as well. Yes, I know you use IS NULL, but if I am reading the TN Article correctly the ISNULL is used for the CHAR(10)..
http://technet.microsoft.com/en-us/library/ms184325.aspx
C. Testing for NULL in a WHERE clause
Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL in the weight column. Note the space between IS and NULL.
USE AdventureWorks2012;
GO
SELECT Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
GO
HTH,
Kent
One thing kdyer just jogged out of my memory ... I see a lot of expressions using the date column [L].insertDT, but I don't see any NULL handling in them. If any [L].insertDT value IS NULL and hits one of these expressions, then that statement will fail.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for skinsfan99's comment #a39491452
for the following reason:
My comment was the only one that pointed to the issue and only after I looked at the code for another hour did it finally jump out.
Amazing how easy it is to miss the obvious and go looking for a more complicated reason.
Accepted answer: 0 points for skinsfan99's comment #a39491452
for the following reason:
My comment was the only one that pointed to the issue and only after I looked at the code for another hour did it finally jump out.
Amazing how easy it is to miss the obvious and go looking for a more complicated reason.
ASKER
Thank you
Looks like experts have provided a ton of help that weeded out problems or gave design advice, so I'm going to object to the closing of this question that no help was provided.
Not to mention, passing a date value in a varchar(10) field is asking for a downstream error.
Not to mention, passing a date value in a varchar(10) field is asking for a downstream error.
ASKER
I never suggested that no help was provided.
I only indicated that the actual problem that was causing the error was not addressed.
Please point out to me where anyone addressed the fact that you cannot concatenate a date to a string and that the issue was with my declaration of the two variables?
You can disagree with my coding, but that still does not address the issue I had.
I only indicated that the actual problem that was causing the error was not addressed.
Please point out to me where anyone addressed the fact that you cannot concatenate a date to a string and that the issue was with my declaration of the two variables?
You can disagree with my coding, but that still does not address the issue I had.
ASKER
I don't mind awarding points.
I guess my frustration is that everyone keeps saying I didn't indicate what datatype @FROMDATE and @TODATE.
It was in my very first post when I asked the question and then again after jimhorn asked "For starters, what is the data type of @FROMDATE and @TODATE?"
I certainly don't want to upset anyone. I have been a member of this site for a long time and it has been my go to when I just can't find a solution or am having a programing issue that I just can't figure out.
I guess my frustration is that everyone keeps saying I didn't indicate what datatype @FROMDATE and @TODATE.
It was in my very first post when I asked the question and then again after jimhorn asked "For starters, what is the data type of @FROMDATE and @TODATE?"
I certainly don't want to upset anyone. I have been a member of this site for a long time and it has been my go to when I just can't find a solution or am having a programing issue that I just can't figure out.
Thanks for the eventual split. Good luck with your project. -Jim
Just for kicks and giggles, everywhere you have a SET @WHERE line, add this line below it, then save to the SP, then rerun it in SSMS and see what gets printed out.
Open in new window
One possibility is that the DATEFORMAT on your QA box is different than the box that is throwing the error.