Solved

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

Posted on 2009-07-02
12
907 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore 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.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

28 Experts available now in Live!

Get 1:1 Help Now