[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-07-02
12
Medium Priority
?
991 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 60

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 60

Accepted Solution

by:
Kevin Cross earned 1000 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 1000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 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