Passing dates to Sql Server 2005 stored procedure

[Purnima]

Hi,

I am trying to execute a SQL Stored procedure from Excel using VBA. Apart
from 2 string inputs, this proc also accepts two datetime imputs. When these
dates are passed via VBA, they are in the format "dd/mm/yyyy. However, the
SQL Server does not seem to accept dates passed in this format.
Applying different date formats in VBA does not help because eventually when
the dates shoot from Excel to SQL Server, they are in the format
"dd/mm/yyyy". I don't want to change the regional settings of the machine,
since it will only be a temporary solution.

This is the part of the code where I pass the date inputs to the procedure:

Dim prm As ADODB.Parameter

Set prm = cmd1.CreateParameter("StartDate", adDate, adParamInput)
cmd1.Parameters.Append prm
cmd1.Parameters("StartDate") = sd

Set prm = cmd1.CreateParameter("EndDate", adDate, adParamInput)
cmd1.Parameters.Append prm
cmd1.Parameters("EndDate").Value = ed


Even though the code does not throw an error, the recordset does not return
anything. If I try passing dates like "2009-01-31" (yyyy-mm-dd), then the
recordset returns the expected output.

Is there any way, I can force Excel to pass dates in the format "yyyy-mm-dd"
without having to change the systems date time format settings?

Thanks,
Purnima

 

[joel]

Excel stores dates as a number with Jan 1, 1900 being the number 1 an
Jan 2, 1900 being the number 2. Each hour is 1/24, and each minute i
1/(24*60).

So Jan 1, 1900 8:00 AM is 1 + 8/24 = 1.333333


You need to convert the excel date to a string using the FORMA
function in VBA


StrDate = Format(MyDate,"yyyy-mm-dd")


Then use StrDate in you SQL statements.

 

[Purnima]

Thanks Joel.

But I am afraid this will not work for me.

We have a number of similar stored procedures all accepting date imputs and
changing the data type of the input is not an option for us. I cannot use
string in place of datetime in the stored procedure.

And because of this, using Format in Excel is not going to help.

I wish Excel would also let us change the International settings instead of
just read them. This would have solved my problem.

 

[joel]

You can still pass the date as a number and not a string. The onl
place you need to convert to a string is on the SQL statement. Exce
will automatically convert a string date back to a number date.