Fisrt of all, this SQL will not execute the stored procedure, it will alter an existing one.
If the proc exists, you need to call it with a command object if you're using ADO.
Here's the ADO code:
Dim cmTime as ADODB.Command
Set cmTime = New ADODB.Command
With cnTime
Set .ActiveConnection = cnServer 'ADO Connection
.CommandType = adCmdStoredProc
.CommandText = "GetServerDate"
.Parameters.Append .CreateParameter("MyDate", adDate, adParamOutput)
.Execute
dLocalDateVariable = .Parameters("MyDate").Value
End With
You'd need to change your stored procedure to return the date *as a date* in an output parameter, but that's no big deal:
Alter Procedure GetServerDate (@MyDate datetime OUTPUT)
As
Begin
Set @MyDate = GetDate()
End
This way, you get the actual date value returned to your app, and you can format it anyway you want. You save memory and execution time by not creating a recordset object and assigning the results of a select query to the recordset, then reading the recordset's field for the value.
If you're using DAO, you're on your own, as I havent used DAO in a while, and the way to do this isn't quite so straightforward. RDO would be better, and actually quite similar to the code above.
Good Luck
Glen
