VBA passing date parameters to SQL Server stored procedure

Question

I am facing an issue with date parameters passed to SQL Server stored procedure from EXCEL VBA code.

The stored procedure taking two date parameters @FromDate and @ToDate. From Excel I want to execute this procedure with the date values taken from cells.

The issue is that, the dates from cells to procedure are passed in the format "dd/mm/yyyy" irrespective of any format changes I do in excel cell. Whereas the SQL procedure is accepting the formats "yyyy-mm-dd" or "yyyy-mon-dd" or "yyyy/mm/dd" or "dd-mon-yyyy".

The procedure is throwing an error :

"Conversion failed when converting date and/or time from character string" for the dates passed from excel (in the format "dd/mm/yyyy"

The vba code I used is (reference https://www.mssqltips.com/sqlservert...to-sql-server/)

Private Sub CommandButton1_Click()
Dim FromDate As Date
Dim ToDate As Date

FromDate = Format(Sheets("Sheet1").Range("B1").Value, "yyyy-mm-dd") 'Pass value from cell B1 to SellStartDate variable
ToDate = Format(Sheets("Sheet1").Range("B2").Value, "yyyy-mm-dd") 'Pass value from cell B2 to SellEndDate variable

MsgBox FromDate
MsgBox ToDate

'Pass the Parameters values to the stored procedure used in the data connection
With ActiveWorkbook.Connections("TestConnection").OLEDBConnection
.CommandText = "EXEC dbo.spr_TestProcedure '" & FromDate & "','" & ToDate & "'"
ActiveWorkbook.Connections("TestConnection").Refresh

End With
End Sub.

Is there any way to pass the dates in the format that SQL Server procedure accepts? If there is no other option, then I will have to take the option to make my procedure parameters type to varchars and convert them to date.

Appreciating your help on this!

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Oct 10, 2016 at 15:20
Declare your FromDate and ToDate variables in VBA as Strings. You want to concatenate strings to your CommandText SQL string. Your Format() is superfluous otherwise. — JNevill
– JNevill, Commented Oct 10, 2016 at 15:45
@marc_s, I have edited my question to tag database which is sql server — missingsemicolon
– missingsemicolon, Commented Oct 10, 2016 at 16:23
@Ralph, Whatever format I use in excel for date, the VBA shows (on msgbox's) the format dd/mm/yyyy. For ex, if I enter 2016-sep-25, msgbox shows it as 25/09/2016. The commandText builds the code as "EXEC dbo.spr_TestProcedure '@FromDate','@ToDate'" which I can see when you right click ->Table->Edit Query. The .Refresh is the one executing the code. — missingsemicolon
– missingsemicolon, Commented Oct 10, 2016 at 16:27
@JNevill, yes it is working when I change the FromDate and ToDate as strings. Thanks for pointing the option. So the table query is built with the date formats which SQL expects. Although I am now stuck that the query is not executing and hence data is not getting refreshed which I except with the code "ActiveWorkbook.Connections("TestConnection").Refresh" — missingsemicolon
– missingsemicolon, Commented Oct 10, 2016 at 16:30

Christian Reinbothe · Accepted Answer · 2021-06-12 07:55:12Z

1

You can pass the date to the sp as char() and convert it to date.

answered Jun 12, 2021 at 7:55

Christian Reinbothe

707 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA passing date parameters to SQL Server stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related