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

I am getting this error when I run this query - where it say the error occured is denoted by (<--------HERE) each statement runs fine but not when togethor
 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; 

Open in new window

LVL 6
r3nderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>INSERT INTO Temp_xControllerQuery( ControllerTime, Location, [Event], [Event Notes] )
Eyeballeth thy above columns, find out which one is a datetime, and then there's the answer, as it appears that you are hard-coding character values to be inserted into them.
Jim HornMicrosoft SQL Server Data DudeCommented:
CREATE TABLE Temp_xControllerQuery (
 ControllerTime DateTime,

INSERT INTO Temp_xControllerQuery( ControllerTime, Location, [Event], [Event Notes]
SELECT cd.UploadTime  As 'ControllerTime',

Either that, or there are values in cd.UploadTime that are not correctly formatted as datetime's, and therefore cannot be inserted into a datetime column.

Just for kicks and giggles, give the below query using ISDATE a whirl and see what happens.

SELECT UploadTime
FROM ControllerData
WHERE ISDATE(UploadTime) = 0

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
r3nderAuthor Commented:
the isDate query did not return anything
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

r3nderAuthor Commented:
Here is what I know
if you pull out the query and run it on its own it runs fine
if you comment out that section of code the error goes to the next select statement
I have checked isDate on all the fields that are datetime and they are fine
r3nderAuthor Commented:
ended up being this section - it seems the case was causing an issue as well; as the Str(N) thanks for your help
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 

Open in new window

r3nderAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.