malawton
asked on
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
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
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
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
ASKER
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.
Did you try my suggestion here as that is what I suspected:
http:#24766244
http:#24766244
ASKER
I don't understand what you want me to do.
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 already tried that and I still get the error.
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
ASKER
Thanks again. This did not work and Line 1 is causing the error.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
results.xls
Open in new window