DateTime param for SP causing BIG headache...!

I've got a stored procedure and one of the parameters is a DateTime. But no matter what I do to the string that's passed into the form for that field, it doesn't like the format. Here's my code:

SqlConnection conn =new SqlConnection(KPFData.getConnectionString());SqlCommand cmd =new SqlCommand("KPFSearchName", conn);cmd.CommandType = CommandType.StoredProcedure;SqlParameter param = cmd.Parameters.Add("@.DOB", SqlDbType.SmallDateTime);param.Direction = dir;param.Value = txtDOB.Text;// also have tried this:param.Value = Convert.ToDateTime(txtDOB.Text);// andparam.Value = Convert.ToDateTime(txtDOB.Text).ToShortDateString;

No matter what I do I always get a formatting error - either I can't convert the string to a DateTime, or the SqlParameter is in the incorrect format, or something along those lines. I've spent a couple hours on this and hoping someone can point out my obvious mistake here...??

Thanks for your help!!

eddie

The problem is as easy as you think: just pass string in correct format to the parameter The format here is the DATEFORMAT setting for current connection to SQL, which you can check by using:

dbcc useroptions

Looking at the 'dateformat' option, you'll see something like 'mdy', which means month-day-year, so '02/13/98' is a valid date while '13/02/98' is invalid (no 13th month). You can pass date in an absolute format of 'YYYY-MM-DD' (e.g. 1998-02-13), which can be recognized if itself is a valid date, no matter which dateformat the connection is using.

Another thing to remember about datetime in SQL is the date range. For more information, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.asp

|||What you had at first should have worked. What are they trying to type into the text field, and what culture is the asp.net thread running as?|||Thanks for the reply!

I'm still having problems, but I think it's an issue with the stored procedure since I'm no longer getting errors, just no results from the query... Is there a way to output the exact string sent to the database with the SP command and all of the parameters?

Thanks again,

eddie|||

Motley:

What you had at first should have worked. What are they trying to type into the text field, and what culture is the asp.net thread running as?

The culture is set as US, but I found out that I was entering the date incorrectly - the stored procedure expects it in this format: mm/dd/yyyy. I never tried using slashes... So I can reformat the text and submit it. Unfortunately I'm still getting jno results from the query, so I'm trying to figure out how to output the exact string sent to the database (see msg above) so I can run some tests directly on the db...

Thanks for your reply!

eddie|||Use the sqlprofiler tool.

Datetime and the missing milliseconds

another datetime problem...

When I send a .net Datetime as a param to a SQL Server sproc, the milliseconds disappear.

I am returning a lastUpdatedDate field from a table in SQL Server to my asp.net page along with updateable table data. The user makes their updates and then submits the form which sends the data to a sproc which makes the update. The procedure makes a check that the lastUpdatedDate submitted back from the form is still the same as the one in the database (to guard against lost updates)

However, when the lastUpdatedDate gets back into SQL server it is missing the milliseconds component - they have been set to 000. I've been tearing my hair out trying to find the point at which the milliseconds disappear and why - but to no avail.

The way I'm doing it is storing the lastUpdatedDate in a .net datetime, passing this into viewstate and then when the user submits the update, retrieving it from ViewState back into the datetime variable and then submitting it to the sproc using a parameter of SqlDbType.Datetime

I have put bits of debug code at every point in the .net processing to look at the datetime and the milliseconds are always there... its only when the value gets picked up in SQL Server that they have disappeared

Any ideas?

MaracatuIs the data type in the data base table DateTime or SmallDateTime? If it's SmallDateTime that might be causing the truncation of the milliseconds (and seconds).|||Its datetime.
The milliseconds go missing when the datetime is retrieved from ViewState after a round trip to the client (bizarrely if the datetime is placed in Viewstate and immediately retrieved and sent to the database no problem occurs).

I have now solved the problem by storing a String value of the datetime, including the milliseconds in ViewState rather than puting the datetime value in there directly.|||Interesting, I now recall having a similar problem with disappearing milliseconds. It wasn't a concern and so I didn't check up on it but I bet that was the reason.

Probably when a datetime is placed into ViewState it serializes it as a string but without milliseconds. But it doesn't actually get serialized until just before the page it sent back to the client so if you stick it into ViewState and pull it back out it's still the original datetime.