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

Can someone please tell me why I keep getting --
Conversion failed when converting the varchar value '06/22/2008' to data type int.
The insert_timestamp and status_timestamp are all datetime

SELECT INSERTDATETIME
, CASE WHEN cast(DATEPART(dy,INSERTDATETIME)/2 AS FLOAT) = CAST(DATEPART(dy,INSERTDATETIME) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
, SUM(NEW_COUNT) AS NEW_COUNT
, SUM(CLOSED_COUNT) AS CLOSED_COUNT
, SUM(NEW_COUNT) -  SUM(CLOSED_COUNT) as DIFFERENCE
FROM (
 
SELECT CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101) AS INSERTDATETIME
	, CASE WHEN cast(DATEPART(dy,INSERT_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,INSERT_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
	, SUM(TOTAL_COUNT) AS NEW_COUNT
	, 0 AS CLOSED_COUNT
	, 0 AS DIFFERENCE
FROM SAMPLEDB
where INSERT_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,INSERT_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,INSERT_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101) AS INSERTDATETIME
	,CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
	, 0 AS NEW_COUNT
	, SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) AS CLOSED_COUNT
	, 0 AS DIFFERENCE
FROM SAMPLEDB
where STATUS_CHANGE_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT  0 AS INSERTDATETIME
	, 0 AS EVEN_IND
	, 0 AS NEW_COUNT
	, 0 AS CLOSED_COUNT
	,SUM(TOTAL_COUNT) - SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) as DIFFERENCE
FROM SAMPLEDB
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
 
) m
GROUP BY INSERTDATETIME, EVEN_IND
ORDER BY INSERTDATETIME, EVEN_IND

Open in new window

malawtonAsked:
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.

pssandhuCommented:
Datepart formula requires a date as a parameter, yours is varchar. Try this
SELECT INSERTDATETIME
, CASE WHEN cast(DATEPART(dy,Cast(INSERTDATETIME, Datetime))/2 AS FLOAT) = CAST(DATEPART(dy,Cast(INSERTDATETIME, Datetime)) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
, SUM(NEW_COUNT) AS NEW_COUNT
, SUM(CLOSED_COUNT) AS CLOSED_COUNT
, SUM(NEW_COUNT) -  SUM(CLOSED_COUNT) as DIFFERENCE
FROM (
 
SELECT CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101) AS INSERTDATETIME
	, CASE WHEN cast(DATEPART(dy,INSERT_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,INSERT_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
	, SUM(TOTAL_COUNT) AS NEW_COUNT
	, 0 AS CLOSED_COUNT
	, 0 AS DIFFERENCE
FROM SAMPLEDB
where INSERT_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,INSERT_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,INSERT_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101) AS INSERTDATETIME
	,CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
	, 0 AS NEW_COUNT
	, SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) AS CLOSED_COUNT
	, 0 AS DIFFERENCE
FROM SAMPLEDB
where STATUS_CHANGE_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT  0 AS INSERTDATETIME
	, 0 AS EVEN_IND
	, 0 AS NEW_COUNT
	, 0 AS CLOSED_COUNT
	,SUM(TOTAL_COUNT) - SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) as DIFFERENCE
FROM SAMPLEDB
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
 
) m
GROUP BY INSERTDATETIME, EVEN_IND
ORDER BY INSERTDATETIME, EVEN_IND

Open in new window

0
pssandhuCommented:
Correction:
SELECT INSERTDATETIME
, CASE WHEN cast(DATEPART(dy,Cast(INSERTDATETIME as Datetime))/2 AS FLOAT) = CAST(DATEPART(dy,Cast(INSERTDATETIME as Datetime)) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
, SUM(NEW_COUNT) AS NEW_COUNT
, SUM(CLOSED_COUNT) AS CLOSED_COUNT
, SUM(NEW_COUNT) -  SUM(CLOSED_COUNT) as DIFFERENCE
FROM (
 
SELECT CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101) AS INSERTDATETIME
	, CASE WHEN cast(DATEPART(dy,INSERT_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,INSERT_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
	, SUM(TOTAL_COUNT) AS NEW_COUNT
	, 0 AS CLOSED_COUNT
	, 0 AS DIFFERENCE
FROM SAMPLEDB
where INSERT_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,INSERT_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,INSERT_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101) AS INSERTDATETIME
	,CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
	, 0 AS NEW_COUNT
	, SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) AS CLOSED_COUNT
	, 0 AS DIFFERENCE
FROM SAMPLEDB
where STATUS_CHANGE_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT  0 AS INSERTDATETIME
	, 0 AS EVEN_IND
	, 0 AS NEW_COUNT
	, 0 AS CLOSED_COUNT
	,SUM(TOTAL_COUNT) - SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) as DIFFERENCE
FROM SAMPLEDB
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
 
) m
GROUP BY INSERTDATETIME, EVEN_IND
ORDER BY INSERTDATETIME, EVEN_IND

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Still looking for what is causing the error as it is usually where there is a concatenation of data where one side is a number and SQL thinks you want to add instead of just joining the strings.  Firstly though, see if it is because you have the formula on outer query that is unnecessary since you already calculated in the derived table.
SELECT INSERTDATETIME
, EVEN_IND
, SUM(NEW_COUNT) AS NEW_COUNT
, SUM(CLOSED_COUNT) AS CLOSED_COUNT
, SUM(NEW_COUNT) -  SUM(CLOSED_COUNT) as DIFFERENCE
FROM (
 
SELECT CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101) AS INSERTDATETIME
        , CASE WHEN cast(DATEPART(dy,INSERT_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,INSERT_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
        , SUM(TOTAL_COUNT) AS NEW_COUNT
        , 0 AS CLOSED_COUNT
        , 0 AS DIFFERENCE
FROM SAMPLEDB
where INSERT_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,INSERT_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,INSERT_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101) AS INSERTDATETIME
        ,CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
        , 0 AS NEW_COUNT
        , SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) AS CLOSED_COUNT
        , 0 AS DIFFERENCE
FROM SAMPLEDB
where STATUS_CHANGE_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT  0 AS INSERTDATETIME
        , 0 AS EVEN_IND
        , 0 AS NEW_COUNT
        , 0 AS CLOSED_COUNT
        ,SUM(TOTAL_COUNT) - SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) as DIFFERENCE
FROM SAMPLEDB
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),CASE WHEN cast(DATEPART(dy,STATUS_TIMESTAMP)/2 AS FLOAT) = CAST(DATEPART(dy,STATUS_TIMESTAMP) AS FLOAT)/2 then 1 else 0 end
 
 
) m
GROUP BY INSERTDATETIME, EVEN_IND
ORDER BY INSERTDATETIME, EVEN_IND

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

malawtonAuthor Commented:
Thanks guys for the quick response.  I have tried both versions and still get the same error.  Each of my outer queries run fine by themselves.  It's when I add the first select statement that causes the error.
0
Kevin CrossChief Technology OfficerCommented:
Did you try my suggestion here as that is what I suspected:
http:#24766244
0
malawtonAuthor Commented:
I don't understand what you want me to do.
0
Kevin CrossChief Technology OfficerCommented:
Instead of:
SELECT INSERTDATETIME
, CASE WHEN cast(DATEPART(dy,INSERTDATETIME)/2 AS FLOAT) = CAST(DATEPART(dy,INSERTDATETIME) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
, SUM(NEW_COUNT) AS NEW_COUNT
, SUM(CLOSED_COUNT) AS CLOSED_COUNT
, SUM(NEW_COUNT) -  SUM(CLOSED_COUNT) as DIFFERENCE

I want you to:
SELECT INSERTDATETIME
, EVEN_IND
, SUM(NEW_COUNT) AS NEW_COUNT
, SUM(CLOSED_COUNT) AS CLOSED_COUNT
, SUM(NEW_COUNT) -  SUM(CLOSED_COUNT) as DIFFERENCE

Since you are already doing this calculation in the queries within the derived table, you can simply use its value instead of trying to calculate all over again.
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
malawtonAuthor Commented:
I already tried that and I still get the error.
0
pssandhuCommented:
Alright, try this version. if this does not work can you please specify which line is the error occuring at. Thx
SELECT INSERTDATETIME
, CASE WHEN cast(DATEPART(dy,Cast(INSERTDATETIME, Datetime))/2 AS FLOAT) = CAST(DATEPART(dy,Cast(INSERTDATETIME, Datetime)) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
, SUM(NEW_COUNT) AS NEW_COUNT
, SUM(CLOSED_COUNT) AS CLOSED_COUNT
, SUM(NEW_COUNT) -  SUM(CLOSED_COUNT) as DIFFERENCE
FROM (
 
SELECT CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101) AS INSERTDATETIME
	, CASE WHEN cast(DATEPART(dy,Cast(INSERT_TIMESTAMP as Datetime))/2 AS FLOAT) = CAST(DATEPART(dy,Cast(INSERT_TIMESTAMP as Datetime)) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
	, SUM(TOTAL_COUNT) AS NEW_COUNT
	, 0 AS CLOSED_COUNT
	, 0 AS DIFFERENCE
FROM SAMPLEDB
where INSERT_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),INSERT_TIMESTAMP,101),
         CASE WHEN cast(DATEPART(dy,Cast(INSERT_TIMESTAMP as Datetime))/2 AS FLOAT) = CAST(DATEPART(dy,Cast(INSERT_TIMESTAMP as Datetime)) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101) AS INSERTDATETIME
	,CASE WHEN cast(DATEPART(dy,Cast(STATUS_TIMESTAMP as Datetime))/2 AS FLOAT) = CAST(DATEPART(dy,Cast(STATUS_TIMESTAMP as Datetime)) AS FLOAT)/2 then 1 else 0 end as EVEN_IND
	, 0 AS NEW_COUNT
	, SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) AS CLOSED_COUNT
	, 0 AS DIFFERENCE
FROM SAMPLEDB
where STATUS_CHANGE_TIMESTAMP is not null
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),
         CASE WHEN cast(DATEPART(dy,Cast(STATUS_TIMESTAMP as Datetime))/2 AS FLOAT) = CAST(DATEPART(dy,Cast(STATUS_TIMESTAMP as Datetime)) AS FLOAT)/2 then 1 else 0 end
 
union all
 
SELECT  '00000000000' AS INSERTDATETIME
	, 0 AS EVEN_IND
	, 0 AS NEW_COUNT
	, 0 AS CLOSED_COUNT
	,SUM(TOTAL_COUNT) - SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) as DIFFERENCE
FROM SAMPLEDB
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),
         CASE WHEN cast(DATEPART(dy,Cast(STATUS_TIMESTAMP as Datetime))/2 AS FLOAT) = CAST(DATEPART(dy,Cast(STATUS_TIMESTAMP as Datetime)) AS FLOAT)/2 then 1 else 0 end
 
 
) m
GROUP BY INSERTDATETIME, EVEN_IND
ORDER BY INSERTDATETIME, EVEN_IND

Open in new window

0
malawtonAuthor Commented:
Thanks again.  This did not work and Line 1 is causing the error.
0
pssandhuCommented:
Could there be instances when the value could be null? Try using the ISNULL() function to set default values.
ISNULL(Field, 0) -- sets the value to 0 when the Filed is null.
P.
 
0
malawtonAuthor Commented:
Thanks. Okay this is what I did.  I made the change in ID: 24767727 above as follows and now it works.:


But now how can I make the columns line up so that it shows the correct difference.

Here is what it looks like now.
SELECT  '00000000000' AS INSERTDATETIME
	, 0 AS EVEN_IND
	, 0 AS NEW_COUNT
	, 0 AS CLOSED_COUNT
	,SUM(TOTAL_COUNT) - SUM(CASE WHEN STATUS NOT IN ('A', 'E', 'N', 'H', 'U') THEN TOTAL_COUNT ELSE 0 END) as DIFFERENCE
FROM SAMPLEDB
GROUP BY CONVERT(VARCHAR(11),STATUS_TIMESTAMP,101),
         CASE WHEN cast(DATEPART(dy,Cast(STATUS_TIMESTAMP as Datetime))/2 AS FLOAT) = CAST(DATEPART(dy,Cast(STATUS_TIMESTAMP as Datetime)) AS FLOAT)/2 then 1 else 0 end
 

Open in new window

results.xls
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 2005

From novice to tech pro — start learning today.