DateTime error message: Conversion failed when converting datetime from character string

Question

I am passing parameters to a stored proc. The parameters code block on the asp.net side is:

SqlConnection con = new SqlConnection(strConn);  
string sqlItemSearch = "usp_Item_Search";  
SqlCommand cmdItemSearch = new SqlCommand(sqlItemSearch, con);  
cmdItemSearch.CommandType = CommandType.StoredProcedure;  

cmdItemSearch.Parameters.Add(new SqlParameter("@Item_Num", SqlDbType.VarChar, 30));  
cmdItemSearch.Parameters["@Item_Num"].Value = txtItemNumber.Text.Trim();  

cmdItemSearch.Parameters.Add(new SqlParameter("@Search_Type", SqlDbType.Int));  
cmdItemSearch.Parameters["@Search_Type"].Value = ddlSearchType.SelectedItem.Value;  

cmdItemSearch.Parameters.Add(new SqlParameter("@Vendor_Num", SqlDbType.VarChar, 10));  
cmdItemSearch.Parameters["@Vendor_Num"].Value = txtVendorNumber.Text.Trim();  

cmdItemSearch.Parameters.Add(new SqlParameter("@Search_User_ID", SqlDbType.Int));  
cmdItemSearch.Parameters["@Search_User_ID"].Value = ddlSeachUser.SelectedItem.Value;  

if (!string.IsNullOrEmpty(txtStartDate.Text))  
{  
    cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));  
    cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime(txtStartDate.Text.Trim());  
}  
else  
{  
    cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));  
    cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime("01/01/1996");  
}  

if (!string.IsNullOrEmpty(txtEndDate.Text))  
{  
    cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));  
    cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(txtEndDate.Text.Trim());  
}  
else  
{  
    cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));  
    cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(DateTime.Now);  
}  
con.Open();  

SqlDataAdapter ada = new SqlDataAdapter(cmdItemSearch);  
DataSet ds = new DataSet();  
ada.Fill(ds);  

gvSearchResults.DataSource = ds;  
gvSearchResults.DataBind();

I tried using

DateTime.ParseExact(this.Text, "dd/MM/yyyy", null);

but I get the same error. The corressponding param in SQL is DateTime. I am currently passing blank fields for @StartDate and @EndDate, so the default values are passed as parameters. The error occurs at ada.Fill(ds) line. What would be causing the error?

possible duplicate of [String was not recognized as a valid DateTime " format dd/MM/yyyy"](http://stackoverflow.com/questions/2193012/string-was-not-recognized-as-a-valid-datetime-format-dd-mm-yyyy) — ChrisF, Jun 30 '10 at 12:21
tried the "DateTime.ParseExact" but still getting the same error :-( — DNR, Jun 30 '10 at 12:28

score 2 · Accepted Answer · edited May 23 '17 at 12:30

2

String was not recognized as a valid DateTime " format dd/MM/yyyy"

this can help you

edited May 23 '17 at 12:30

Community

1
1

answered Jun 30 '10 at 12:19

Serkan Hekimoglu

4,234
5
40
64

1

Should be posted as a comment - you have enough rep. – ChrisF Jun 30 '10 at 12:21
sorry Im new on Stack Over Flow. Will be careful next time. Thank you. – Serkan Hekimoglu Jun 30 '10 at 12:25
tried the "DateTime.ParseExact" but still getting the same error :-( – DNR Jun 30 '10 at 12:28
you are getting your datetime value from textBox right? if yes, do it after getting the value. DateTime newDate = new DateTime(Convert.ToInt32(textBox.Text.Substring(x,y))),Convert.ToInt32(textBox.Text.Substring(x,y))),Convert.ToInt32(textBox.Text.Substring(x,y))); new DateTime( requires 3 parameters which are Year,Month,Day ) with using substring put the required parameters in new DateTime(HERE) – Serkan Hekimoglu Jun 30 '10 at 12:44
why would you convert to Int32? – DNR Jun 30 '10 at 12:56
Because you are trying to get a numeric value from textBox.Text. If you dont Convert it to Int32, you get an exception. – Serkan Hekimoglu Jun 30 '10 at 13:02
but I am converting it to DateTime. – DNR Jun 30 '10 at 13:06
you are not converting it to datetime. With my code, you are creating a new date time with the numeric values getting from textBox. – Serkan Hekimoglu Jun 30 '10 at 13:13
I am passing default values to the stored proc. (01/01/1996 & DateTime.Now). The StartDate & EndDate textboxes are blank. – DNR Jun 30 '10 at 13:29
at your code, you get an exception in this part Convert.ToDateTime(txtEndDate.Text.Trim()); and you solve the problem, with what I wrote. – Serkan Hekimoglu Jun 30 '10 at 13:51
That line is NOT executed. The ELSE section gets executed. – DNR Jun 30 '10 at 13:53
Seriously, I dont understand how Convert.ToDateTime(DateTime.Now) and DateTime.Now throws exception. because this error message Conversion failed when converting datetime from character string. looks like throwing from textBox.Text.Trim() part. I gonna search this. – Serkan Hekimoglu Jun 30 '10 at 14:09

score 1 · Answer 2 · answered Jun 30 '10 at 14:12

1

and the solution is.......

cmdItemSearch.Parameters["@EndDate"].Value = DateTime.Now;

not the Convert.ToDateTime(DateTime.Now);

answered Jun 30 '10 at 14:12

DNR

3,706
14
56
91

score 0 · Answer 3 · answered Jun 30 '10 at 12:23

Check the culture the database is configured to use and make sure the date/time being passed in (as configured by the CurrentCulture), is compatible.

If you don't have control over the culture the database is using, you can force it to accept a specific format by prepending SET DATEFORMAT yada yada to your script, e.g.:

SET DATEFORMAT ymd;

SELECT ... WHERE [StartDate] = @StartDate

score 0 · Answer 4 · answered Jun 30 '10 at 12:23

0

The default .NET DateTime isn't a valid SQL DateTime value; that's what the error is coming from. If you're going to pass a DateTime parameter for search,but don't have a specific value to search on, you should provide something in SQL's DateTime value range that will work for all your searches.

answered Jun 30 '10 at 12:23

Cylon Cat

7,111
2
25
33

can you provide an sample string? – DNR Jun 30 '10 at 12:29
The minimum value for SQL Server DateTime values is January 1, 1753. If you're looking for a minimum date, that's it. Also, for default DateTime values, rather than converting a text string, just create a new DateTime, like this: DateTime defaultDate = new DateTime(1753, 1, 1); – Cylon Cat Jun 30 '10 at 13:07

DateTime error message: Conversion failed when converting datetime from character string

4 Answers4