krisred
asked on
Syntax error converting character string to smalldatetime data type.
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--
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
'dbo.fnEncideXml(''Nursery
'dbo.fnEncodeXml(''Willame
'CONVERT(VARCHAR,dtmDateAp
'dbo.fnEncodeXml(SUB.strCh
'dbo.fnEncodeXml(SUB.strEP
'dbo.fnEncodeXml(COALESCE(
'COALESCE(b.dblTotalAmount
'COALESCE(d.strUnitAltName
'FROM dbo.tblSAWorkOrder a ' +
'INNER JOIN dbo.tblSAWorkOrderChemical
'ON a.strWorkOrderNumber = b.strWorkOrderNumber ' +
'LEFT JOIN ' +
'(SELECT ' +
'c.intChemicalProductID, ' +
'c.strChemicalProductAltNa
'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
'FROM ' +
'dbo.tblSAWorkOrderPlantTa
'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
'COALESCE(SUB1.strTargetTy
'COALESCE(d.strUnitAltName
'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
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
SET @strXML = @strXML + COALESCE('<UseLocationZipC
SET @strXML = @strXML + '</UseLocation>'
END
SELECT @ptrval = TEXTPTR(XmlUseReport)
FROM #tblXmlUseReport
UPDATETEXT #tblXmlUseReport.XmlUseRep
SET @strXML = ''
SELECT @strXML = @strXML +
'<UseReportProduct>' +
COALESCE('<epaProductNumbe
COALESCE('<otherProductNum
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
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
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--
ASKER
I tried that, but still getting the same error
ASKER
please close this question - I figured it out
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I solved it by storing the date as varchar instead of smalldatetime
Why do I get the feeling this will come back to bite you ...
'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