String was not recognized as a valid DateTime

[Herby]

Hi im relatively new to both .NET and ADO.NET.
Im having a problem with dates when using SQL.

I have something like:
effective_date = '1/1/1990'

Iv then tried things like
effective_date = {ts '1990-01-01 00:00:00'}

Whatever i try i keep getting the following exception.

String was not recognized as a valid DateTime

Is there not some conversion routine, whereby if i give it some ad-hoc
string which represents a date in its many forms i.e. 1-1-1990, it will
return me the correct format to use in an SQL expression????

Thanks.

 

[Miha Markic [MVP C#]]

Hi Herby,

You should really look into parametrised queries (instead of concatenating
sql commands you would pass values as parameters).
There is plenty of docs on the topic and you might start from .net help
file.

 

[Herby]

I was using parameters. Its ok iv sussed it now.

String^ effective_date
....

effective_date = "1/1/1990";

DateTime effDate;

effDate.Parse(effective_date);

You map the SQL parameter to effDate which you specify as type
DateTime.

It then internally does any needed conversions

Everything is now cool.

 

[Herby]

I do have one additional problem:

It is now not complaining about the SQL text, but is not returning any
rows, when it should be.
I need to see the final constructed SQL text after the markers have
been substituted for their parameter values.
Is there a way for me to see the final SQL as submitted to the DBMS?

Thanks.