Conversion failed when converting the varchar value 'Jan ' to data type int.

Question

1.00/5 (3 votes)

See more:

Hi Guys,

Anyone has an idea why this code is generating this error msg
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'Jan ' to data type int.

DECLARE @Period	DATETIME 
SET @Period = '2013/01/01'


SELECT     Division, DSCustName, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' AND 
                      [Quarter] = '1' THEN ISNULL(Amount, 0) ELSE 0 END AS Year1Qtr1, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' AND 
                      [Quarter] = '2' THEN ISNULL(Amount, 0) ELSE 0 END AS Year1Qtr2, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' AND 
                      [Quarter] = '3' THEN ISNULL(Amount, 0) ELSE 0 END AS Year1Qtr3, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' AND 
                      [Quarter] = '4' THEN ISNULL(Amount, 0) ELSE 0 END AS Year1Qtr4, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS TotalYear1, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' AND [Quarter] = '1' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS Year2Qtr1, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' AND [Quarter] = '2' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS Year2Qtr2, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' AND [Quarter] = '3' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS Year2Qtr3, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' AND [Quarter] = '4' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS Year2Qtr4, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' THEN ISNULL(Amount, 0) ELSE 0 END AS TotalYear2, 
CASE WHEN ISNULL(Product, '') = '' THEN 'No Product Group Name' ELSE Product END as Name
FROM         TableName 
WHERE ([Year] = LEFT(@Period, 4) OR [Year] = CASE WHEN CAST(RIGHT(@Period, 2) as INT) <= 6 THEN LEFT(@Period, 4) - 1 ELSE LEFT(@Period, 4) + 1 END)

Posted 7-Feb-14 6:42am

Smith2014

Updated 7-Feb-14 7:18am

Richard C Bishop

v3

Add a Solution

Comments

Richard C Bishop 7-Feb-14 12:43pm

It's pretty clear is it not? You are trying to convert a varchar to an int. Find where that is occurring and fix it.

Smith2014 7-Feb-14 12:48pm

Did you look at the code at all?

Richard C Bishop 7-Feb-14 12:58pm

It is not necessary for me to look at the code. I know exactly what that means. All you need to do is debug this and make the necessary changes. It says line 5, so I would start there. Your [Quarter] is probably 'Jan' since that is the first comparison with an int.

Smith2014 7-Feb-14 13:06pm

I will appreciate a second opinion on it. seems you dont even understand what You're saying

Richard C Bishop 7-Feb-14 13:11pm

Really? What is unclear about the exception that is being thrown and the way to fix it? You can't compare a varchar to an int, that is what you are doing. So you need to find where that is taking place(Line 5 as it states) and fix it. How is that at all ambiguous?

Smith2014 7-Feb-14 13:12pm

If i can fix it.. I won't post it here..

Richard C Bishop 7-Feb-14 13:20pm

Well I found the issue, would you like to know it?

Maarten Kools 7-Feb-14 13:22pm

Tease ;)

Richard C Bishop 7-Feb-14 13:39pm

I would never.....

Smith2014 7-Feb-14 13:21pm

yes!

Richard C Bishop 7-Feb-14 13:32pm

I believe the declaration of "@Period" as a DATETIME is skewing the data you think you are getting. If "@Period" is not going to be dynamic and will always be '2013/01/01' then I would make it a VARCHAR(15) data type. If it is a dynamic date, then try converting that date into the varchar format specified in your setting of "@Period" variable before casting it and getting the RIGHT of LEFT side of the date. Let me know if that needs clarification.

Smith2014 7-Feb-14 15:03pm

Thank You Bishop! Error Msg resolved

Richard C Bishop 7-Feb-14 15:15pm

You are welcome. I am going to submit my message as the solution so it can be removed from the unanswered queue. You may accept it as the solution if you wish.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard C Bishop · Answer 1 · 2014-02-07T09:15:00