I have the following stored procedure, when I run I am getting an error:
Syntax error converting character string to smalldatetime data type.
I can't seem to figure out what the problem is??
---BEGIN PROC ---
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.spGetPURSXmlExport
(
@dtmStartDate SMALLDATETIME,
@dtmEndDate SMALLDATETIME
)
AS
SET NOCOUNT ON
DECLARE @strSQL VARCHAR(8000),
@strXML VARCHAR(8000),
@strTmpProducts VARCHAR(150),
@strSiteCategory VARCHAR(255),
@strLSiteName VARCHAR(255),
@strWaterBasin VARCHAR(255),
@strZipCode VARCHAR(10),
@strdtmDate VARCHAR(10),
@strLChemicalProductName VARCHAR(500),
@strLEPARegNumber VARCHAR(100),
@strLSLN VARCHAR(100),
@strLSection18 VARCHAR(100),
@strLPurpose VARCHAR(255),
@strdblAmount VARCHAR(255),
@strUnit VARCHAR(100),
@strRandomString VARCHAR(5),
@ptrval BINARY(16)
CREATE TABLE #tblUseReport
(
strSiteCategory VARCHAR(255) NOT NULL,
strSiteName VARCHAR(255) NULL,
strWaterBasin VARCHAR(255) NULL,
strZipCode VARCHAR(10) NULL,
strdtmDate VARCHAR(50) NULL,
strChemicalProductName VARCHAR(500) NULL,
strEPARegNumber VARCHAR(100) NULL,
strSLN VARCHAR(100) NULL,
strSection18 VARCHAR(100) NULL,
strPurpose VARCHAR(255) NULL,
strdblAmount VARCHAR(255) NULL,
strUnit VARCHAR(100) NULL
)
CREATE TABLE #tblXmlUseReport
(
XmlUseReport TEXT
)
SET @strSQL = ' INSERT INTO #tblUseReport( ' +
'strSiteCategory, ' +
'strSiteName, ' +
'strWaterBasin, ' +
'strdtmDate, ' +
'strChemicalProductName, ' +
'strEPARegNumber, ' +
'strPurpose, ' +
'strdblAmount, ' +
'strUnit) ' +
'SELECT dbo.fnEncodeXml(''Agricult
ure''), ' +
'dbo.fnEncideXml(''Nursery
/Christmas
Trees''), ' +
'dbo.fnEncodeXml(''Willame
tte''), ' +
'CONVERT(VARCHAR,dtmDateAp
plied,101)
' +
'dbo.fnEncodeXml(SUB.strCh
emicalProd
uctAltName
), ' +
'dbo.fnEncodeXml(SUB.strEP
ARegNumber
), ' +
'dbo.fnEncodeXml(COALESCE(
SUB1.strTa
rgetTypeAl
tName,''No
ne'')), ' +
'COALESCE(b.dblTotalAmount
,''0''), ' +
'COALESCE(d.strUnitAltName
,''None'')
' +
'FROM dbo.tblSAWorkOrder a ' +
'INNER JOIN dbo.tblSAWorkOrderChemical
s b ' +
'ON a.strWorkOrderNumber = b.strWorkOrderNumber ' +
'LEFT JOIN ' +
'(SELECT ' +
'c.intChemicalProductID, ' +
'c.strChemicalProductAltNa
me, ' +
'c.strEPARegNumber, ' +
'c.ysnNonReportable ' +
'FROM ' +
'dbo.tblChemicalProducts c ' +
') AS SUB ON b.intChemicalID = SUB.intChemicalProductID ' +
'LEFT JOIN ' +
'dbo.tblUnits d ' +
'ON b.intRateSUnitID = d.intUnitID ' +
'LEFT JOIN ' +
'(SELECT ' +
'e.strWorkOrderNumber, ' +
'MAX(g.strTargetTypeAltNam
e) AS strTargetTypeAltName ' +
'FROM ' +
'dbo.tblSAWorkOrderPlantTa
rget e ' +
'INNER JOIN ' +
'dbo.tblTargetTypeTarget f ' +
'ON e.intTargetID = f.intTargetID ' +
'LEFT JOIN ' +
'dbo.tblTargetType g ON ' +
'f.intTargetTypeID = g.intTargetTypeID ' +
'GROUP BY ' +
'e.strWorkOrderNumber) AS SUB1 ON a.strWorkOrderNumber = SUB1.strWorkOrderNumber ' +
'WHERE a.intWorkOrderStage = ''7'' AND a.ysnScoutOnly <> ''1'' AND SUB.ysnNonReportable <> ''1'' ' +
'AND a.dtmDateApplied BETWEEN ' + @dtmStartDate + ' AND ' + @dtmEndDate +
'GROUP BY a.dtmDateApplied, ' +
'SUB.strEPARegNumber, ' +
'SUB.strChemicalProductAlt
Name, ' +
'COALESCE(SUB1.strTargetTy
peAltName,
''None''),
' +
'COALESCE(d.strUnitAltName
,''None'')
' +
'ORDER BY a.dtmDateApplied '
exec (@strSQL)
SET @strXML = '<?xml version="1.0" encoding="UTF-8"?>'
SET @strXML = @strXML + '<!DOCTYPE Submission SYSTEM "
http://purs-reports.oda.state.or.us/WebObjects/PURSReporter.woa/Contents/WebServerResources/purs.dtd">'
SET @strXML = @strXML + '<Submission version="1.0">'
INSERT INTO #tblXmlUseReport VALUES(@strXML)
SET @strXML = ''
DECLARE UserReport CURSOR FOR
SELECT strSiteCategory, strSiteName, strWaterBasin, strZipCode, strdtmDate
FROM #tblUseReport
GROUP BY strSiteCategory, strSiteName, strWaterBasin, strZipCode, strdtmDate
OPEN UserReport;
FETCH NEXT FROM UserReport INTO
@strSiteCategory, @strLSiteName, @strWaterBasin, @strZipCode, @strdtmDate
-- @strSiteCategory, @strLSiteName, @strWaterBasin, @strZipCode, @strdtmDate, @strLChemicalProductName, @strLEPARegNumber, @strLSLN, @strLSection18, @strLPurpose, @strdblAmount, @strUnit
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC spGenerateRandomString @strRandomString OUTPUT
SET @strXML = '<UseReport id="' + @strRandomString + '">'
SET @strXML = @strXML + '<SiteCategory>' + LTRIM(RTRIM(@strSiteCatego
ry)) + '</SiteCategory>'
SET @strXML = @strXML + COALESCE('<SpecificSite>' + @strLSiteName + '</SpecificSite>', '')
SET @strXML = @strXML + COALESCE('<useDate>' + @strdtmDate + '</useDate>', '')
IF ISNULL(@strZipCode, '') <> '' OR ISNULL(@strWaterBasin, '') <> ''
BEGIN
SET @strXML = @strXML + '<UseLocation>'
SET @strXML = @strXML + COALESCE('<UseLocationWate
rBasin><wa
terBasin>'
+ @strWaterBasin + '</waterBasin></UseLocatio
nWaterBasi
n>', '')
SET @strXML = @strXML + COALESCE('<UseLocationZipC
ode><zipCo
de>' + @strZipCode + '</zipCode></UseLocationZi
pCode>', '')
SET @strXML = @strXML + '</UseLocation>'
END
SELECT @ptrval = TEXTPTR(XmlUseReport)
FROM #tblXmlUseReport
UPDATETEXT #tblXmlUseReport.XmlUseRep
ort @ptrval NULL 0 @strXML
SET @strXML = ''
SELECT @strXML = @strXML +
'<UseReportProduct>' +
COALESCE('<epaProductNumbe
r>' + CONVERT(VARCHAR, strEPARegNumber) + '</epaProductNumber>', '') +
COALESCE('<otherProductNum
ber>' + CASE
WHEN strEPARegNumber IS NULL AND strSLN IS NOT NULL THEN strSLN
WHEN strEPARegNumber IS NULL AND strSection18 IS NOT NULL THEN strSection18
ELSE NULL
END
+ '</otherProductNumber>', '') +
COALESCE('<productName>' + strChemicalProductName + '</productName>', '') +
COALESCE('<purpose>' + strPurpose + '</purpose>', '') +
COALESCE('<quantity>' + strdblAmount + '</quantity>', '') +
COALESCE('<quantityUnit>' + strUnit + '</quantityUnit>', '') +
'</UseReportProduct>'
FROM #tblUseReport
WHERE strSiteCategory = @strSiteCategory
AND COALESCE(strSiteName, '') = COALESCE(@strLSiteName, '')
AND COALESCE(strWaterBasin, '') = COALESCE(@strWaterBasin, '')
AND COALESCE(strZipCode, '') = COALESCE(@strZipCode, '')
AND COALESCE(strdtmDate, '') = COALESCE(@strdtmDate, '')
SET @strXML = '<products>' + @strXML + '</products></UseReport>'
SELECT @ptrval = TEXTPTR(XmlUseReport)
FROM #tblXmlUseReport
UPDATETEXT #tblXmlUseReport.XmlUseRep
ort @ptrval NULL 0 @strXML
SET @strXML = ''
SET @strTmpProducts = ''
FETCH NEXT FROM UserReport INTO
@strSiteCategory, @strLSiteName, @strWaterBasin, @strZipCode, @strdtmDate
END
CLOSE UserReport
DEALLOCATE UserReport
SET @strXML = '</Submission>'
SELECT @ptrval = TEXTPTR(XmlUseReport)
FROM #tblXmlUseReport
UPDATETEXT #tblXmlUseReport.XmlUseRep
ort @ptrval NULL 0 @strXML
SET @strXML = ''
SELECT XmlUseReport FROM #tblXmlUseReport
DROP TABLE #tblXmlUseReport
DROP TABLE #tblUseReport
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---END PROC--
'SELECT dbo.fnEncodeXml(''Agricult
'dbo.fnEncideXml(''Nursery
'dbo.fnEncodeXml(''Willame
'CONVERT(VARCHAR,dtmDateAp
you appear to be trying to insert a date, but are changing it to VARCHAR.
Don't do the convert to varchar here, but first insert it as a date and then whenever you retrieve it, convert to varchar if that is what you want.
Just a thought