Solved

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

Posted on 2009-07-02
12
922 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:malawton
  • 5
  • 4
  • 3
12 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24766235
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24766243
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24766244
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:malawton
ID: 24766427
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24766474
Did you try my suggestion here as that is what I suspected:
http:#24766244
0
 

Author Comment

by:malawton
ID: 24767008
I don't understand what you want me to do.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24767042
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
 

Author Comment

by:malawton
ID: 24767128
I already tried that and I still get the error.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24767727
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
 

Author Comment

by:malawton
ID: 24774491
Thanks again.  This did not work and Line 1 is causing the error.
0
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 250 total points
ID: 24774519
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
 

Author Comment

by:malawton
ID: 24774791
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Count where two id types exist in column 8 29
date diff with Fiscal Calendar 4 55
Format Data Field - SQL 11 37
awk and Pythagoras? 5 19
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question