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.