Syntax error converting datetime from character string.

Question

Post reply

Syntax error converting datetime from character string.

conrad 81955

SSC Rookie

Points: 25
More actions
February 2, 2011 at 4:07 pm

#95691

Hello,
I am attempting to call a stored procedure from Excel VBA using ADO. I need to pass in a list of dates. However, I keep getting this error: Syntax error converting datetime from character string.
Here is what I used to create the stored procedure:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_GetResults
@CustomerNumber nvarchar(15),
@DateList nvarchar(1000)
AS
BEGIN
SELECT convert(varchar(10),CD.DateCollected,101) as DateCollected, CD.HoursFasted,
P.WHFDescription, TG.GroupName, T.TestDescription, TR.TestResult, TR.Abnormal,
TR.RefRange + ' ' + TR.Units as ReferenceRange,
CONVERT(Char(8), CD.DateCollected, 112) + Replicate('0', 3 - LEN(Convert(VarChar(3), P.SortOrder))) + Convert(VarChar(3), P.SortOrder) + Replicate('0', 3 - LEN(Convert(VarChar(3), TG.GroupOrder))) + Convert(VarChar(3), TG.GroupOrder) AS CalcSortOrder
FROM CustomerDraws CD
INNER JOIN TestResults TR ON TR.CustomerDrawNumber = CD.CustomerDrawNumber
INNER JOIN LabTests LT ON LT.LabID = CD.LabID AND LT.TestCode = TR.TestCode
INNER JOIN Tests T ON T.TestID = LT.TestID
INNER JOIN TestGroups TG ON TG.TestGroupID = T.TestGroupID
INNER JOIN CustomerProfiles CP ON CP.CustomerDrawNumber = CD.CustomerDrawNumber
INNER JOIN ProfilesTests PT ON PT.TestID = T.TestID AND PT.ProfileID = CP.ProfileID
INNER JOIN Profiles P ON P.ProfileID = PT.ProfileID
WHERE CD.CustomerNumber = @CustomerNumber
AND CD.DateCollected IN(@DateList)
AND P.WHFDescription Not IN('Blood Type','Blood Pressure','Body Fat Analysis')
ORDER BY CalcSortOrder
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Here is the VBA snipit calling the stored procedure:
'open recordset
Set adoCmd = New ADODB.Command
adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = "sp_GetResults"
adoCmd.CommandType = adCmdStoredProc
adoCmd.Parameters.Refresh
adoCmd.Parameters(1).Value = strCustNbr
adoCmd.Parameters(2).Value = "'" & strSPDates & "'"
Set adoRS = adoCmd.Execute
The date list that is being passed looks like:
'''1/28/2011 8:47:04 AM'',''1/29/2010 7:42:15 AM'',''9/24/2009 9:14:42 AM'',''1/30/2009 8:14:53 AM'''
I have also tried executing the stored procedure from SQL Analyzer using:
DECLARE @RC int
DECLARE @CustomerNumber nvarchar(15)
DECLARE @DateList nvarchar(1000)
-- Set parameter values
set @DateList = '''1/28/2011 8:47:04 AM'',''1/29/2010 7:42:15 AM'',''9/24/2009 9:14:42 AM'',''1/30/2009 8:14:53 AM'''
set @CustomerNumber = 'W03002435'
EXEC @RC = [Roundup].[dbo].[sp_GetResults] @CustomerNumber, @DateList
I get the same error doing this.
Any help would be greatly appreciated. Thank you in advance.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Ok, you are trying to look for a date in a string.

Here is the problem:

AND CD.DateCollected IN(@DateList)

@Datelist is an NVARCHAR (by the way, why waste processing time handling unicode for a date string?)

you can't compare a datetime (presuming that DateCollected is a DATETIME) to a string like that.

What you need to do is split the string @DateList out into seperate datetime values and use that set of dates in the WHERE clause.

This has been covered many times here and elsewhere and a quick google for "passing comma delimited parameters to a stored procedure" will throw up answers.

You might also want to read this thread about it - I would start at the end and work backwards to find the consensus of opinion....

http://www.sqlservercentral.com/Forums/Topic966348-2761-1.aspx

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);