Insert statement generated using SQLParameter and datetime.now()

Question

Hi there I have a SQL Datetime field in sql 2000. I have a stored proc called addPresentation. From my .Net code I add a sqlparameter as:-

new SqlParameter("Date", SqlDbType.DateTime).Value=DateTime.now

When I call the stored proc from code and observe sql profiler it is attempting to perform the insert as :-

exec addPresentation @Name='Gary Thorpe',@Date=' '2008-11-28 00:00:00:000' ',@Duration=60

As you can see the issue is the 2 single quotes each side of the date to be inserted. But how are these coming about? Have been wracking my brain for ages on this one. Any helpers?

Answer 1

Personally I prefer cmd.Parameters.AddWithValue("@Date", DateTime.Now)

But what is the point of your question? Is the date value passed correctly? Is the parameter passed as a date? Is this an issue of data displayed by Sql Profiler?

Answer 2

oops my bad! it was a typo in my code its in there without the brackets so still got the same issue. Have just tried it with datetime.today and got the same problem. weird

Answer 3

My question is why is the datetime surrounded with two single quotes as opposed to one?

Answer 4

Have you tried splitting it out into multiple statements? I was getting some some odd problem in C# whereby it does not like the single statement...

SqlParameter p = new SqlParameter("Date", SqlDbType.DateTime).Value = DateTime.Now;

But if I split out to this...

SqlParameter p = new SqlParameter("Date", SqlDbType.DateTime);
p.Value = DateTime.Now;

It seems to like it. I believe there is a an issue with an implicit conversion (which is why the compiler threw a wobbly) in the first example that is avoided in the second example.

May not help but worth a try :)

Answer 5

I finally worked this out. The reason for the problem was down to my return value from SQL. The query was getting executed however I was trying to return the @@IDENTITY using return instead of Select. (Not used SQL for a while). It seems that in profiler with SQL 2005 that date params do get surrounded with 2 single quotes each side which I found a little strange given when i copy and paste this into SSMS this errors. But hey there you go. Would love to know why this is the case though.

Cheers for the responses!

Answer 6

Please tell me you aren't using @@identity! Use scope_identity() instead. @@identity does not give the correct identity value some of the time (If someone adds a trigger that inserts toa table with an identity it will return that identity not the one you just inserted) and can cause major havoc in your database.