Prev: MsgBox refer to seperate worksheet
Next: Recalculate a worksheet following an autofilter selection.
From: Purnima on 7 Jun 2010 05:50
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
From: joel on 7 Jun 2010 06:05

Excel stores dates as a number with Jan 1, 1900 being the number 1 and
Jan 2, 1900 being the number 2. Each hour is 1/24, and each minute is
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 FORMAT
function in VBA


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


Then use StrDate in you SQL statements.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=207503

http://www.thecodecage.com/forumz

From: Purnima on 7 Jun 2010 09:39
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" wrote:

>
> Excel stores dates as a number with Jan 1, 1900 being the number 1 and
> Jan 2, 1900 being the number 2. Each hour is 1/24, and each minute is
> 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 FORMAT
> function in VBA
>
>
> StrDate = Format(MyDate,"yyyy-mm-dd")
>
>
> Then use StrDate in you SQL statements.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=207503
>
> http://www.thecodecage.com/forumz
>
> .
>
From: joel on 7 Jun 2010 11:09

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=207503

http://www.thecodecage.com/forumz

 | 
Pages: 1
Prev: MsgBox refer to seperate worksheet
Next: Recalculate a worksheet following an autofilter selection.