The conversion of a varchar data type to a datetime data typ

Question

i am getting  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. public void Insert(String Query) { MyCommand.CommandType = CommandType.Text; MyCommand.CommandText = Query; MyConnection.Open(); MyCommand.Connection = MyConnection; MyCommand.ExecuteNonQuery(); MyConnection.Close(); }

Iftikar Hussain · Answer

Hi Try like this Month = StartDate.Substring(0 2);//value 07 Day = StartDate.Substring(2 2);//value 22 Year = StartDate.Substring(4 2); // 13 int year = DateTime.Now.Year; int cent = year / 100; cent = (cent*100)+Year; NewDate = new DateTime(cent Month Day); Regards Iftikar

Sanjeeb Lenka · Answer

refer to this link http://social.msdn.microsoft.com/Forums/en-US/f5b6b628-ee32-4d27-a733-9d902ee14e48/error-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in-an-outofrange try to use parameter to pass the value in insert statement.

Manj N · Answer

hi can you tell me which book is good to learn c# programming and sql .

Iftikar Hussain · Answer

it means your date value is not in correct format. For ex. month value can be max 12 but if you passed 13 then you will get this error. Regards Iftikar

Manj N · Answer

sir i want to know why exactly this problem comes? The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

Iftikar Hussain · Answer

try like this String Query; String DeveloperId; String ProjectID; String ActivityId; String RowExist; String Totaldayhour; String InsertQry; StartDate=GetFormattedDate(StartDate); InsertDate=GetFormattedDate(InsertDate); Query = Select Id FROM TblTeam WHERE FirstName=' + User.Identity.Name + '; DeveloperId = Value.SelectQry(Query); Query = Select ProjectId FROM TblProjects WHERE ProjectName=' + ProjectName.SelectedValue.ToString() + '; ProjectID = Value.SelectQry(Query); Query = Select count(*) as a from TblTimeSheets Where DevId = ' + DeveloperId + ' AND ProjectId = ' + ProjectID + ' AND WeekStartDate=' + StartDate + '; RowExist = Value.SelectQry(Query); Query = Select Id FROM TblActivityMaster WHERE Activity=' + MVActivity.SelectedValue.ToString() + '; ActivityId = Value.SelectQry(Query); Query = INSERT INTO TimeSheetBreakDown(DevIdProjectIdActivityDescriptionAdditionalDescriptionHourDayMonthYearWeekStartDateDateEstimated); Query += VALUES(' + DeveloperId + '' + ProjectID + '' + ActivityId + '; Query += ' + MVtxt.Text + '' + txtAdditionalDesc.Text + '' + MVddl.SelectedValue.ToString() + '' + WeekDay + '' + Month + '' + Year + '; Query += ' + StartDate + '' + InsertDate + '' + cbestimate.Checked + '); Value.Insert(Query); public string GetFormattedDate(string datevalue) { string Month = datevalue.Substring(0 2);//value 07 string Day = datevalue.Substring(2 2);//value 22 string Year = datevalue.Substring(4 2); // 13 int year = DateTime.Now.Year; int cent = year / 100; cent = (cent*100)+Convert.ToInt32(Year); DateTime NewDate = new DateTime(cent Convert.ToInt32(Month) Convert.ToInt32(Day)); return NewDate.ToShortDateString(); } Regards Iftikar

Sanjeeb Lenka · Answer

what type of error you are getting?

Manj N · Answer

my code works correctly in visual studio 2005 but it gives error in 2010..

Sanjeeb Lenka · Answer

try this one: string StartDate = 07-01-13;// what the value you are passing string Month = StartDate.Substring(0 2);//value 07 string Day = StartDate.Substring(3 2);//value 22 string Year = StartDate.Substring(6 2); // 13 DateTime NewDate = DateTime.ParseExact(Day + / + Month + / + Year dd/MM/yy CultureInfo.InvariantCulture);

Manj N · Answer

start date is 07-01-13

Sanjeeb Lenka · Answer

what is the value in StartDate you are passing.

Manj N · Answer

another problem sir Month = StartDate.Substring(0 2);//value 07 Day = StartDate.Substring(2 2);//value 22 Year = StartDate.Substring(4 2); // 13 NewDate = DateTime.ParseExact(Month + / + Day + / + Year dd/mm/yy CultureInfo.InvariantCulture); /* when i parse this then i get NewDate as {07-01-2013 00:22:00}.. but i want 07-22-2013*/

Sanjeeb Lenka · Answer

plz provide some sample value what you are passing for StartDate and InsertDate

Manj N · Answer

i tried everything.. then also it gives error-> The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

Sanjeeb Lenka · Answer

try this query: it will take date as mm/dd/yyyy try to pass like that. Query = INSERT INTO TimeSheetBreakDown(DevIdProjectIdActivityDescriptionAdditionalDescriptionHourDayMonthYearWeekStartDateDateEstimated); Query += VALUES(' + DeveloperId + '' + ProjectID + '' + ActivityId + '; Query += ' + MVtxt.Text + '' + txtAdditionalDesc.Text + '' + MVddl.SelectedValue.ToString() + '' + WeekDay + '' + Month + '' + Year + '; Query += Convert(datetime' + StartDate + '103)Convert(datetime' + InsertDate + '103)' + cbestimate.Checked + ');

Manj N · Answer

from string to mm/dd/yy

Sanjeeb Lenka · Answer

which format of date you are passing

Manj N · Answer

sir i tried as you said then it gives String was not recognized as a valid DateTime. error.

Sanjeeb Lenka · Answer

try this for insert it may help you: Query = INSERT INTO TimeSheetBreakDown(DevIdProjectIdActivityDescriptionAdditionalDescriptionHourDayMonthYearWeekStartDateDateEstimated); Query += VALUES(' + DeveloperId + '' + ProjectID + '' + ActivityId + '; Query += ' + MVtxt.Text + '' + txtAdditionalDesc.Text + '' + MVddl.SelectedValue.ToString() + '' + WeekDay + '' + Month + '' + Year + '; Query += ' + Convert.ToDateTime(StartDate) + '' + Convert.ToDateTime(InsertDate) + '' + cbestimate.Checked + ');

Manj N · Answer

public void Insert(String Query) { MyCommand.CommandType = CommandType.Text; MyCommand.CommandText = Query; MyConnection.Open(); MyCommand.Connection = MyConnection; MyCommand.ExecuteNonQuery();//The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. MyConnection.Close(); }

Manj N · Answer

public void Insert(String Query) { MyCommand.CommandType = CommandType.Text; MyCommand.CommandText = Query; MyConnection.Open(); MyCommand.Connection = MyConnection; MyCommand.ExecuteNonQuery(); MyConnection.Close(); }

Manj N · Answer

MVtxt.Text = Value.Validate(MVtxt.Text); if (MVActivity.SelectedIndex != 0 && MVtxt.Text !=  && MVddl.SelectedIndex != 0) { String Query; String DeveloperId; String ProjectID; String ActivityId; String RowExist; String Totaldayhour; String InsertQry; Query = Select Id FROM TblTeam WHERE FirstName=' + User.Identity.Name + '; DeveloperId = Value.SelectQry(Query); Query = Select ProjectId FROM TblProjects WHERE ProjectName=' + ProjectName.SelectedValue.ToString() + '; ProjectID = Value.SelectQry(Query); Query = Select count(*) as a from TblTimeSheets Where DevId = ' + DeveloperId + ' AND ProjectId = ' + ProjectID + ' AND WeekStartDate=' + StartDate + '; RowExist = Value.SelectQry(Query); Query = Select Id FROM TblActivityMaster WHERE Activity=' + MVActivity.SelectedValue.ToString() + '; ActivityId = Value.SelectQry(Query); Query = INSERT INTO TimeSheetBreakDown(DevIdProjectIdActivityDescriptionAdditionalDescriptionHourDayMonthYearWeekStartDateDateEstimated); Query += VALUES(' + DeveloperId + '' + ProjectID + '' + ActivityId + '; Query += ' + MVtxt.Text + '' + txtAdditionalDesc.Text + '' + MVddl.SelectedValue.ToString() + '' + WeekDay + '' + Month + '' + Year + '; Query += ' + StartDate + '' + InsertDate + '' + cbestimate.Checked + '); Value.Insert(Query);

Iftikar Hussain · Answer

Hi Please provide your query details Regards Iftikar

Sanjeeb Lenka · Answer

plz share you Sql Query. so we can solve your problem.