ADODB - parameters with date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Shouldn't that be yyyy-mm-dd, Alex? (In other words, aren't date separators
necessary?)
 
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.
 
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
 
Back
Top