ADODB - parameters with date

M

Martin

I have a function created in ADP using ADODB to talk to our SQL Server. The
function is now required to accept two parameters, BeginDate and EndDate,
both are in date format of 'mm/dd/yyyy'. I have trouble trying to pass these
date parameters and hope you could help.
Thank you!

The line below, I changed the adInteger to adDBDate and changed the 4 to 8,
and still didn't work.

..Parameters.Append .CreateParameter("@DateValue", adInteger, _
adParamInput, 4, BeginMonth)


'The following is the full function module for your reference.
'-------------------------------------------------------------------
Function DateRangeTaken(ByVal BeginDate, EndDate As Date, ByVal PlanCode As
Integer, ByVal EmplID As string) As Currency

Dim GLOBAL_Timekeep_Total As Currency

GLOBAL_Timekeep_Total = 0

Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
If EntireMonth = 1 Then
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "SQLProcedureA"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@ID", adChar, _
adParamInput, 20, EmplID)
.Parameters.Append .CreateParameter("@DateValue", adInteger, _
adParamInput, 4, BeginDate)
.Parameters.Append .CreateParameter("@DateValue2r", adInteger, _
adParamInput, 4, EndDate)
.Parameters.Append .CreateParameter("@Catergory", adInteger, _
adParamInput, 4, PlanCode)
Set rst = .Execute

End With


Do While Not rst.EOF

GLOBAL_Timekeep_Total = GLOBAL_Timekeep_Total + rst.Fields("hrs_col") +
(rst.Fields("mins_col") / 60)
rst.MoveNext
Loop
 
D

Douglas J Steele

Shouldn't that be yyyy-mm-dd, Alex? (In other words, aren't date separators
necessary?)
 
R

RoyVidar

Douglas J Steele wrote in message
Shouldn't that be yyyy-mm-dd, Alex? (In other words, aren't date separators
necessary?)
Click to expand...

I think SQL-server will accept that string as a date. Bol (2005) states
this, and the yyyy-mm-dd format to be valid ISO 8601 date formats,
which
also seems consistant with http://en.wikipedia.org/wiki/ISO_8601 (basic
vs extended "for human readability" formats)

But this would mean converting the date to string before passing it as
a parameter, and I think, have SQL server perform implicit conversion
to date again, within the SP (which shouldn't be a problem, though), vs
your recommandation of using adDate, which is also what I would use.
 
M

Martin

Dear Alex, Douglas and Royvidar:
Thank you for your help! It finally worked!!!
Although I wasn't able to use the adDate apporach with two date formats
suggested, when I apply Royvidar's approach, I've used the adChar in the
ADODB statement, and keep the parameter of SQL Stored Procedure in
smalldatetime data type, and let the 'implicit' conversion run the magic,
and waalaaa!! It ran beautifully and gave me a precise dataset.
Thanks again for working together on this issue.
Martin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL server Insert 2
How do you call a stored procedure that returns a value? 3
How do you pass parameters to a stored procedure 2
ADO: how to execute SQL Server stored procedure and get return val 1
Getting a Return Value from a Stored Procedure in VBA 2
Numeric Data type 3
Call SQLServer UDF with decimal parameter 3
Setting Connection Property to pass 3 parameters to an SQL Proc. 2

Top