DateTime confusion.

Hi All,

I'm stumped. I've got a stored procedure with a number of input parameters, and working fine.

I added two extra input parameters, FromDate datetime, ToDate datetime. I have not even included these in the SQL yet and just tried to execute the stored proc alone with manually inputting paramters, but I keep getting error: @.FromDate: this input parameter cannot be converted.

That surely means a formatting issue, so I copy and pasted a value directly from the database into this parameter field, and still get the same error. I've tried various formats, with single and double quotes, and without. But just dont know?

And even when I populate these parameters in my code and call the stored proc, it returns no results either, even though I haven't included these new date paramters in the SQL select, so that means it was in error and no doubt a formatting issue on those date fields.

format I used was: 17/07/2007 00:00:00

I tried to populate the parameters via the code as follows:

Dim dt As DateTime
DateFromTB1.Text = DateTime.Now
DateTime.TryParse(DateFromTB1.Text, dt)
SqlDataSource1.SelectParameters("FromDate").DefaultValue = dt

I know there is an extra step for now (DateFromTB1.Text = DateTime.Now), this step will fall away and just parse the textbox.text field as entered by the user.

Any help appreciated, thanks.

Try this:

string strDate = Convert.ToDateTime(txtDate.Text).ToString("MM/dd/yyyy");

if you want to pass it as string or

DateTime dtDate = Convert.ToDateTime(txtDate.Text).ToUniversalTime();

or

DateTimeFormatInfo myDTFI =newCultureInfo("en-GB",false).DateTimeFormat;

DateTime Dtime =Convert.ToDateTime("31/12/2007", myDTFI);

or

DateTime dtDate = Convert.ToDateTime(txtDate.Text).ToShortDateStrin(); if you are using smalldatatime in your databse.

Also there is a .ToLocalTime() function if that might be any help to you too.

Hope this would help.

|||

Hi Mehdi,

thanks for the quick response, I've since discovered that even though the SQL Server Express date format, as displayed in my Show Table Data windows, is dd/mm/yyyy, it is actually expecting mm/dd/yyyy format via my stored proc.

My computer regional settings are dd/mm/yyy, but somehow SQL server settings, or perhaps it's my VWD Express settings are the American version, and I cannot find where to change it. any ideas? thanks.

|||

You can either use SET DATEFORMAT or be more efficient and send the data in YYYYMMDD format irrespective of client's location and not have to worry about converting mdy to dmy to viceversa.

|||

thanks for the help, that did the trick.