The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

 IF Object_id('Temp_xControllerQuery', 'U') IS NOT NULL
 DROP TABLE Temp_xControllerQuery;  
 CREATE TABLE Temp_xControllerQuery (
 ControllerTime DateTime,
 Location varchar(20),
 [Event] varchar(30),
 [Event Notes] varchar(255)
  
 );
 IF Object_id('Temp_precont', 'U') IS NOT NULL
DROP TABLE Temp_precont;  
CREATE TABLE Temp_precont  (
id int IDENTITY(1,1) PRIMARY KEY,  
toolnumber int,  
starttime datetime,  
endtime datetime,  
JobType varchar(10),  
[Event Notes] varchar(255)
);  
IF Object_id('Temp_precont2', 'U') IS NOT NULL
DROP TABLE Temp_precont2;  
CREATE TABLE Temp_precont2  (  
id int IDENTITY(1,1) PRIMARY KEY,  
toolnumber int,  
starttime datetime,  
endtime datetime,  
JobType varchar(10)  
);  
INSERT INTO Temp_precont( toolnumber, starttime,JobType )  
SELECT ToolNumber, UploadTime,JobType  
FROM ToolAssemblies  
WHERE ControllerSN =1006  ;  
INSERT INTO Temp_precont2( toolnumber, starttime,JobType )  
SELECT ToolNumber, UploadTime,JobType   
FROM ToolAssemblies   WHERE ControllerSN = 1006  ;  
UPDATE Temp_precont   
SET Temp_precont.endtime =   (  
SELECT starttime FROM Temp_precont2 WHERE Temp_precont.id=Temp_precont2.id - 1   
);  
UPDATE Temp_precont 
SET Temp_precont.endtime = GETDATE() 
WHERE Temp_precont.endtime IS NULL;   
INSERT INTO Temp_xControllerQuery( ControllerTime, Location, [Event], [Event Notes] )<-------------------------------------HERE
SELECT 
cd.UploadTime  As 'ControllerTime',l.Location AS 'Location','Tool Communication' AS 'Event',
('Job Circulating Hours: '  + 
cast(round(cd.JobCirculatingHours,2) as varchar(30)) +';'+' Job Powered On: ' + 
cast(round(cd.JobPoweredOnHours,2) as varchar(30)) +';'+' Job Number of Pulses: ' + cast(cd.JobNumberOfPulses as varchar(20)) +';'+' Job Start Date: ' + cast(cd.JobStartDate as varchar(30))) AS 'Event Notes'  
FROM ControllerData as cd  
LEFT JOIN SerializedAssets as sa ON sa.SerialNumber = cd.SerialNumber  
LEFT JOIN Locations AS l ON cd.DefaultLocationIndex = l.LocationIndex  
WHERE sa.ToolType = 'CO' AND cd.SerialNumber = 1006  
GROUP BY cd.UploadTime, l.Location,cd.JobCirculatingHours,cd.JobPoweredOnHours,cd.JobNumberofPulses,cd.JobStartDate 


UNION ALL  
(  SELECT crt.CalibrationTime AS 'ControllerTime' ,l.Location AS 'Location','Roll Test' AS 'Event',
('Angle = '+ Str(Avg(crt.Angle),10,2)+';'+' Temp: '+ Str(Avg(crt.Temp),10,1)+';'+' Range: '+ Str(Max(crt.Angle),10,2)+' - '+ Str(Min(crt.Angle),10,2)) AS 'Event Notes'  
FROM ControllerRollTest AS crt  
JOIN Locations AS l ON crt.DefaultLocationIndex = l.LocationIndex  
GROUP BY crt.CalibrationTime, crt.SerialNumber, crt.DefaultLocationIndex,l.Location  
HAVING crt.SerialNumber = 1006  
UNION ALL  
(  
SELECT case csct.CalibrationTime WHEN '0000-00-00 00:00:00' THEN GETDATE() Else csct.CalibrationTime end  AS 'ControllerTime' ,
l.Location AS 'Location','Spot Correction' AS 'Event',
('Angle = '+ Str(Avg(csct.Angle),10,2)+';'+' Temp: '+ Str(Avg(csct.Temp),10,1)+';'+' Range: '+ Str(Max(csct.Angle),10,2)+' - '+ Str(Min(csct.Angle),10,2)) AS 'Event Notes'  
FROM ControllerSpotCorrectionTest AS csct  
JOIN Locations AS l ON csct.DefaultLocationIndex = l.LocationIndex  
GROUP BY csct.CalibrationTime, csct.SerialNumber, csct.DefaultLocationIndex,l.Location  
HAVING (((csct.SerialNumber)= 1006))  )  
UNION ALL 
(  
SELECT ta.UploadTime AS 'ControllerTime',l.Location as 'Location','Assembly' AS 'Event',
('ToolNumber: '+cast(ta.ToolNumber as varchar)+';'+' Battery num: '+cast(ta.BatterySN as varchar)+';'+' BatteryGauge: '+ CAST(ta.BatteryGaugeSN as varchar)+';'+' Sensor Pack: '+cast(ta.MagneticsSN as varchar)+';'+' Tool Size: '+ cast(ta.ToolSize as varchar)+';'+' JobType: '+ta.JobType) AS 'Event Notes'  
FROM ToolAssemblies ta  
JOIN Locations AS l ON ta.DefaultLocationIndex = l.LocationIndex   
WHERE ta.ControllerSN = 1006  
Group By ta.UploadTime,ta.ControllerSN,ta.DefaultLocationIndex,l.Location,ta.ToolNumber,ta.BatterySN,ta.BatteryGaugeSN,ta.MagneticsSN,ta.ToolSize,ta.JobType  
)  
UNION  ALL
(  SELECT jde.starttime as 'ControllerTime',CAST(j.jobid AS char(10)) as 'Location','Job Downhole Equipment' As 'Event',
('ToolNumber: '+cast(p.toolNumber as varchar)+';'+' Tool Type: '+ p.JobType+';'+' Run Number: '+cast(jde.RunID as varchar)+';'+ 'Tool Size: '+ cast(jde.ToolSize as varchar)+';'+' Company: '+j.Company+';'+' Rig: '+j.Rig+' '+ cast(j.RigNumber as varchar)+';'+' Well Name: '+j.WellName) AS 'Event Notes'  
FROM JobDownholeEquipment AS jde  
LEFT JOIN Temp_precont as p ON jde.ToolNumber = p.toolnumber  
JOIN Job AS j on j.JobID = jde.JobID  
WHERE jde.starttime >= p.starttime and jde.endtime<= p.endtime  )  
UNION ALL (  
SELECT NotesDate as 'ControllerTime','' as 'Location','Repair' As 'Event',Notes AS 'Event Notes'  
FROM ControllerNotes  Where Serialnumber = 1006  
)  
UNION ALL (  
SELECT MAX(iis.UploadDate) as 'ControllerTime',l.Location as 'Location', 'Reconcile' as 'Event', 'Parts History reconcile' as 'Event Notes'  
FROM Inventory_SerializedAssets iis  
JOIN   Locations as l ON l.LocationIndex = iis.LocationID  
WHERE iis.SerialNumber =  1006  AND iis.ToolType = 'CO'  
AND iis.UploadDate BETWEEN         '2012-01-01 00:00:00' AND '2015-12-31 23:59:59'   
GROUP BY l.location)
);  
SELECT * FROM Temp_xControllerQuery 

ORDER BY ControllerTime DESC; 

Select allOpen in new window