Hi I previously posted a question about this issue before, the answers I got did fix the problem but its reared its ugly head again. Have a to and from date which gets passed into the stored procedure and then I use them to count the records with an incident date between the to and from dates passed in, if they meet other conditions with in the statement.
The incident date in the database table is a varchar( I cant change this)
The code is below :-
select COUNT(pkCATSafetyAreaID) AS 'Count', T1.Category, T1.CategoryID from tblIncidentCATSSafetyAreas T1
INNER JOIN tblIncidentCATSUnsafeDetails T2 ON PATINDEX('%,'+CAST(T1.pkCATSafetyAreaID AS varchar(200))+',%',','+T2.SafetyAreas+',') > 0
inner join tblIncident T3 on T2.fkIncidentID = T3.pkIncidentID
inner join Admin_System.dbo.tblLocation T4 on T3.fkLocationID = T4.pkLocationID
WHERE Category = 'Personal Protective Equipment (PPE)'
AND CONVERT(datetime, t3.IncidentDate,105) BETWEEN CONVERT(datetime, @FromDate,105) AND CONVERT(datetime, @ToDate,105)
AND PATINDEX('%,'+cast(@pkLocationID as varchar(100))+',%',Sortkey) > 0
AND T4.Type LIKE '%'+@locationType+'%'
AND t3.ArchivedOn IS NULL
GROUP BY pkCATSafetyAreaID, T1.Category, T1.CategoryID, SafetyArea
The error I get when this is run is :- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Any help with this matter would be much appreciated.