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
skinsfan99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
skinsfan99Author Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

skinsfan99Author Commented:
OK Jim, I have attached the SP.   Thanks for your help!!
0
skinsfan99Author Commented:
Guess it would help to actually attach the file....
SP-Code.txt
0
Kent DyerIT Security Analyst SeniorCommented:
I am a little late to the party as it were..  I think you have your logic reversed.  When I used your version, I got the exact same error.

I just tried this against our prod database and works great.

DECLARE @FROMDATE DATETIME
DECLARE @TODATE DATETIME
DECLARE @WHERE NVARCHAR(50)

SET @FROMDATE = '1/1/2013'
-- SET @TODATE = '1/1/2013'

SELECT COUNT(*) FROM SOMETABLE (NOLOCK)

--SET @WHERE = ' WHERE CONVERT(VARCHAR(10), L.[insertDT], 101) >= ''' + @FROMDATE + ''

IF @FROMDATE IS NOT NULL
BEGIN
SET @WHERE = ' WHERE L.[insertDT] >= ' + CONVERT(VARCHAR(10), @FROMDATE, 101)
END
     
IF @TODATE IS NOT NULL
BEGIN
IF @WHERE IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND L.[insertDT] <= ' + CONVERT(VARCHAR(10), @TODATE, 101)
END     
ELSE     
BEGIN
SET @WHERE = ' WHERE L.[insertDT] <= ' + CONVERT(VARCHAR(10), @TODATE, 101)
END                       
END     

Open in new window


HTH,

Kent
0
skinsfan99Author Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>have you had any further thoughts after reviewing my SP file??
Only to use the 'SELECT @where' methodology in various parts of your SP, and test.
Especially by copying what is printed, pasting in a new window, and executing to make sure it works.

Good luck.
Jim
0
Kent DyerIT Security Analyst SeniorCommented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Scott PletcherSenior DBACommented:
You're missing the ending quotes on the dates in your initial code, as underlined below.

But you shouldn't use "101" as the date format, use 'YYYYMMDD' (112) instead: the latter always works, the former can produce errors if the local server is DATEFORMAT dmy.


IF @FROMDATE IS NOT NULL
BEGIN
SET @WHERE = ' WHERE CONVERT(VARCHAR(8), L.[insertDT], 112) >= ''' + @FROMDATE + ''''
END
     
IF @TODATE IS NOT NULL
BEGIN
IF @WHERE IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND CONVERT(VARCHAR(8), L.[insertDT], 112) <= ''' + @TODATE + ''''
END      
ELSE      
BEGIN
SET @WHERE = ' WHERE CONVERT(VARCHAR(8), L.[insertDT], 112) <= ''' + @TODATE + ''''
END                        
END
0
skinsfan99Author Commented:
The issue was related to something I had in my 1st and 3rd posting and completely overlooked:

@FROMDATE            DATETIME,
@TODATE                  DATETIME

In my code I am trying to concatenate a date to a string.

This is not valid and what was causing the error:

SET @WHERE = ' WHERE CONVERT(VARCHAR(10), L.[insertDT], 101) >= ''' + @FROMDATE + ''

I changed the declaration  to VARCHAR(10) as it should have been all along and now everything is working as expected.  

@FROMDATE             VARCHAR(10),
@TODATE                  VARCHAR(10)

Thanks to everyone for their input!!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skinsfan99Author Commented:
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.
0
skinsfan99Author Commented:
Thank you
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
skinsfan99Author Commented:
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.
0
skinsfan99Author Commented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the eventual split.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.