We help IT Professionals succeed at work.

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

I am having an issue with this query does anyone know why? therror is
Conversion failed when converting the varchar value '; ' to data type int.

 Select * FROM(  SELECT JobDownholeEquipment.StartTime as 'StartTime', 
 Cast(Job.JobID as char(25)) AS 'Location', 'Job' AS 'Event',  
 ('Tool Type: '+ Case JobType WHEN 0 Then 'surefire' else 'wildfire' end + '; '+'Run Number: '+ JobDownholeEquipment.RunID + '; ' + 'Tool Size: ' + JobDownholeEquipment.ToolSize + '; ' +   'Company: ' + Job.Company + '; ' + 'Rig: '+ Job.Rig + '; ' + 'Rig Number: ' +  Job.RigNumber + '; ' + 'Well Name: '+ Job.WellName ) AS 'Event Notes'  
 FROM (Job INNER JOIN JobDownholeEquipment ON Job.JobID = JobDownholeEquipment.JobID) 
 INNER JOIN SerializedAssets ON JobDownholeEquipment.ToolNumber = SerializedAssets.SerialNumber  
 WHERE JobDownholeEquipment.ToolNumber = 2 
 AND SerializedAssets.ToolType = 'TN' AND Job.Company <> 'Test Company'           
 AND JobDownholeEquipment.StartTime BETWEEN          '2012-01-01 00:00:00' AND '2015-12-31 23:59:59'   
 UNION  SELECT ToolAssemblies.UploadTime as 'StartTime', l.Location as 'Location', 'Tool Communication' AS 'Event', 
 ('Tool Size: ' + ToolSize + '; '+ 'Controller: '+ControllerSN +'; '+ 'Battery Gauge: '+BatteryGaugeSN +'; '+ 'Battery: ' + BatterySN + '; '+ 'Magnetics: '+ MagneticsSN) AS 'Event Notes'  
 FROM ToolAssemblies  JOIN   Locations as l ON l.LocationIndex = ToolAssemblies.DefaultLocationIndex  
 WHERE ToolAssemblies.ToolNumber= 2           AND ToolAssemblies.UploadTime BETWEEN          '2012-01-01 00:00:00' AND '2015-12-31 23:59:59'          
 UNION         SELECT iis.UploadDate as 'StartTime',l.Location as 'Location', '' as 'Event', 
 'Parts History reconcile' as 'Event Notes'          FROM Inventory_SerializedAssets iis         
 JOIN   Locations as l ON l.LocationIndex = iis.LocationID          
 WHERE iis.SerialNumber = 2  AND iis.ToolType = 'TN'        
 AND iis.UploadDate 
 BETWEEN            '2012-01-01 00:00:00' AND '2015-12-31 23:59:59'    )X  
 Order By StartTime DESC

Open in new window

Comment
Watch Question

Database Consultant
Top Expert 2009
Commented:
replace 'Run Number: '+ JobDownholeEquipment.RunID + '; '

with

'Run Number: '+ cast( JobDownholeEquipment.RunID as varchar )  + '; '

Do similar casting for all the integer data types
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
AND ToolAssemblies.UploadTime BETWEEN '2012-01-01 00:00:00' AND '2015-12-31 23:59:59'

23:59:59 is NOT the end of a day (there are sub-second units to cater for)

the end of '2015-12-31' is actually '2016-01-01 00:00:00 +00000'

You should not use BETWEEN for dates ranges because of this see: "Beware of Between"

Author

Commented:
Thanks Aneesh and Porter