Date conversion error from Excel 2003 to SQL Server 2005

Question

Date conversion error from Excel 2003 to SQL Server 2005

born2bongo

SSCrazy

Points: 2477
More actions
October 27, 2008 at 4:24 am

#214454

My store proc has the param: @StartDate datetime.
My vb code was working until I threw the date parameter in the mix. I've tried a lot of different things but the current version of the code is:
Dim ParamDate As Date
.
.
stADO = "Provider=SQLOLEDB.1;Data Source=" & stdatasource & "; User Id=" & _
stUser & "; Password=" & stPwd & ";"
.
.
ParamDate = Range("ParmList").Cells(RowCounter, 2).Value
Set objPar1 = objCom.CreateParameter(Trim(Range("ParmList").Cells(RowCounter, 1).Value), _
adDate, adParamInput, , _
ParamDate)
When I run the store procedure, entering the date as 01/10/2008, or 01-Oct-2008, is fine. When I run it as passed by VB, #01/10/2008#, I get:
"Msg 8114, Level 16, State 5, Procedure usp_Extract_Adhoc_Report, Line 0
Error converting data type nvarchar to datetime."
The execution code generated is:
"EXEC@return_value = [dbo].[usp_Extract_Adhoc_Report]
@LOB = N'GTI',
@Tower = N'DTS',
@StartDate = N'#01/10/2008#'"
How can I resolve this?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

One option would be to accept the date parameter as a string and then do a string.replace() to strip out the invalid characters. I don't know VB, but the .NET languages, and TSQL won't recognize the value you are passing as a date because of the # signs inside the string.