Hi I need to gather data from the database where an Incident Date is in between the FromDate and ToDate variables. The IncidentDate Column is a varchar field and dates are saved in the following format dd-mm-yyyy, the FromDate and ToDate are initially strings when they are passed across.
The main issue seems to be that the statement is inside a loop and is set to a variable then executed. Please find the Code below :-
SET NOCOUNT ON
DECLARE @sql nvarchar(2000)
DECLARE @COUNT int
SET @COUNT = 1
Declare @FromDate1 datetime
Declare @ToDate1 datetime
SET @FromDate1 = CONVERT(datetime, @FromDate,105)
SET @ToDate1 = CONVERT(datetime, @ToDate,105)
WHILE @COUNT < 6
set @sql = 'SELECT T2.pkRCCauseOptionID as ''RCType'+CAST(@COUNT as varchar(10))+''', CauseOption as ''condition'+CAST(@COUNT as varchar(10))+''', COUNT(RCType) as ''count'+CAST(@COUNT as varchar(10))+'''
FROM tblIncidentCause T1
INNER JOIN tblIncidentRootCauseOptions T2 ON T1.RCType = T2.pkRCCauseOptionID
INNER JOIN tblIncident t3 on t1.fkIncidentID = t3.pkIncidentID
INNER JOIN tblLocation t4 on t3.fkLocationID = t4.pkLocationID
INNER JOIN tblIncidentDetails f ON f.fkIncidentID=T3.pkIncidentID
WHERE PATINDEX(''%,'+CAST(@pkLocationID as varchar(500))+',%'',Sortkey) > 0
AND CONVERT(datetime,t3.IncidentDate,105) >= '+@FromDate1+' AND CONVERT(datetime,t3.IncidentDate,105) <= '+@ToDate1+'
AND SUBSTRING(CauseOption ,1 , 3) = '''+CAST(@dataType as varchar(10))+'.'+CAST(@COUNT as varchar(10))+'''
AND t3.fkIncidentType = 21
AND t4.Type LIKE ''%'+@locationType+'%''
AND f.typeofincident IN (1,2,10)
GROUP BY CauseOption, T2.pkRCCauseOptionID'
exec sp_executesql @sql
SET @COUNT = @COUNT+1 --count up used for loop and within the statement to get data
With this code I get the following error :- when Msg 241, Level 16, State 1, Procedure SP_v3_incident_report_root
ts_data, Line 34
Conversion failed when converting date and/or time from character string.
I have also tried the following line of code within the WHERE Clause for checking the Incident Date
AND CONVERT(datetime,t3.IncidentDate,105) BETWEEN CONVERT(datetime, '+CAST(@FromDate as varchar(20))+',105) AND CONVERT(datetime, '+CAST(@ToDate as varchar(20))+',105)
This does no flag up an error when being used but also doesn't bring back any data.
Any help will be much appreciated