VB.Net SqlDbType.DateTime Question

[alex_read]

I have a SQL Server stored procedure which accepts a datetime parameter to filter it's resulting data by. This has the following statement declared at the start:

SET DATEFORMAT dmy

Through the SQL Query Analyzer, I can run this stored procedure, using either of the following, and receive results:

SET @FromDate = '30-12-2004 00:01:01:001'
SET @FromDate = '30/12/2004'

Placing exactly the same strings in the following code within vb.net, produces a "String was not recognized as a valid DateTime." exception message! Can someone please explain how I can pass a date value into the SQLClient.SQLCommand object successfully???

cmdSQL = New SqlClient.SqlCommand
 
cmdSQL.Parameters.Add("@FromDate", SqlDbType.DateTime).Value= ?????
cmdSQL.Parameters.Item(0).Direction = ParameterDirection.Input

Thanks!!

[kaffenils]

Try to convert the string to a datetime with System.Convert.ToDateTime(strValue) before passing it as a parameter to the SQLCommand.

[alex_read]

Hi kaffenils - thanks for the reply!

I've tried this one and it resulted in an "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." exception - however I format the date I pass to the parameter.

Thanks again!

[techgnome]

I suspect the problem lies in the fact that VB doesn't know about the DATEFORMAT setting (which doesn't get set until the SP runs). With that said, there's two things that come to mind, both of which are a little shady.

1) Just set the date parameter formated in the "default" mdy. Not sure how it will interpret it. I'd suggest trying it in a "safe" area.

2) We used to this with SQLServer7 and Crystal. Change your parameter type to a string, then pass in the parameter in as a string, formatted the way you want. Inside the SP, after SETting DATEFORMAT, DECLARE a datetime variable, and CONVERT the string parameter into the date variable.

Like I said, both are a little hoaky, but one of them should work.

Tg

[alex_read]

Hey thanks for those!

I was thinking of number 2 myself, passing this into the stored procedure as a string, then performing a cast etc on it, but surely there must be a professional / proper way to do this?

It just seemed odd that have MS put in a datetime option on the SQLDBType enumeration if this doesn’t work.

Thanks for the suggestions

[techgnome]

But the problem is that the client code doesn't know about a server side setting -- which doesn't even get set until the SP actualy runs. That's why it errors out on the VB end.

Tg

[alex_read]

Oh sorry - I see what you mean now, because I have the dateformat setting. Sorry I'm with it now! Ok thanks for that!!!