目前,我将date参数作为varchar传递并进行转换,因为我在传递date参数时出错。我仍然得到错误,但它是一个转换参数。
Sub GetPositions() 'xdate As Date
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cmd = New ADODB.Command
Set prm = New ADODB.Parameter
Sheets("Positions").Select
Range("a2:bb999999").ClearContents
Set cn = New ADODB.Connection
cn.CommandTimeout = 300000
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=GlobalR;Data Source=SWP"
Dim d As Date
d = "2013/12/03"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Get9599Delta"
cmd.NamedParameters = True
Set prm = cmd.CreateParameter("@date", adVarChar, adParamInput, 12)
cmd.Parameters.Append prm
cmd.Parameters("@date").Value = "'12/3/2013'"
Set rs = New ADODB.Recordset
rs.Open cmd.Execute ''''I AM GETTING AN ERROR ON THIS LINE THAT READS
''''''''''' CONVERSION FAILED WHEN CONVERTING DATETIME TO CHARACTER STRING
Cells(2, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
以下是我存储的流程
alter PROC Get9599Delta (@date varchar(12))
AS
DECLARE @d DATETIME
SET @d = CONVERT(DateTime, @date, 104) --convert the varchar to a date
你知道为什么我会有这个转换问题吗?
发布于 2013-12-07 01:32:44
通过将SET NOCOUNT ON
放在我的存储过程的顶部,解决了这个问题!
发布于 2013-12-06 00:11:35
试试这个:
.Parameters.Append .CreateParameter("@date", adDate, adParamInput, , )
@是可选的msdn ( command.CreateParameter (Name, Type, Direction, Size, Value) )
和参数adDBTimeStamp的类型
https://stackoverflow.com/questions/20404605
复制相似问题