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

Jim Horn

For starters, what is the data type of @FROMDATE and @TODATE?

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.

SELECT @where

Open in new window

One possibility is that the DATEFORMAT on your QA box is different than the box that is throwing the error.

skinsfan99

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

Jim Horn

>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?

skinsfan99

ASKER

OK Jim, I have attached the SP. Thanks for your help!!

skinsfan99

ASKER

Guess it would help to actually attach the file....
SP-Code.txt

SOLUTION

Kent Dyer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

skinsfan99

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

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kent Dyer

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.

USE AdventureWorks2012;
GO
SELECT Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
GO

Open in new window

HTH,

Kent

Jim Horn

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

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

skinsfan99

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

skinsfan99

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.

skinsfan99

ASKER

Thank you

Jim Horn

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.

skinsfan99

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.

skinsfan99

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.

Jim Horn

Thanks for the eventual split. Good luck with your project. -Jim