Solved

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

Posted on 2009-07-02
12
903 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

'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 …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now