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

Manj N

13y2.7kVisual Basic .NET

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();

}

24 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted 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
0
Manj N13y ago
hi,
can you tell me, which book is good to learn c# programming and sql .
0
Sanjeeb Lenka13y ago
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.
0
Iftikar Hussain13y ago
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
0
Manj N13y ago
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.

0
Iftikar Hussain13y ago
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(DevId,ProjectId,Activity,Description,AdditionalDescription,Hour,Day,Month,Year,WeekStartDate,Date,Estimated)";
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
0
Sanjeeb Lenka13y ago
what type of error you are getting?
0
Manj N13y ago
my code works correctly in visual studio 2005, but it gives error in 2010..
0
Sanjeeb Lenka13y ago
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);
0
Manj N13y ago
start date is 07-01-13
0
Sanjeeb Lenka13y ago
what is the value in StartDate you are passing.
0
Manj N13y ago
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*/
0
Sanjeeb Lenka13y ago
plz provide some sample value what you are passing for StartDate and InsertDate
0
Manj N13y ago
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."
0
Sanjeeb Lenka13y ago
try this query:
it will take date as mm/dd/yyyy try to pass like that.

Query = "INSERT INTO TimeSheetBreakDown(DevId,ProjectId,Activity,Description,AdditionalDescription,Hour,Day,Month,Year,WeekStartDate,Date,Estimated)";
            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 + "')";
0
Manj N13y ago
from string to mm/dd/yy
0
Sanjeeb Lenka13y ago
which format of date you are passing
0
Manj N13y ago
sir,
i tried as you said, then it gives "String was not recognized as a valid DateTime." error.
0
Sanjeeb Lenka13y ago
try this for insert it may help you:

Query = "INSERT INTO TimeSheetBreakDown(DevId,ProjectId,Activity,Description,AdditionalDescription,Hour,Day,Month,Year,WeekStartDate,Date,Estimated)";
            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 + "')";
0
Manj N13y ago
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();
}

0
Manj N13y ago
public void Insert(String Query)
{
MyCommand.CommandType = CommandType.Text;
MyCommand.CommandText = Query;
MyConnection.Open();
MyCommand.Connection = MyConnection;
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
0
Manj N13y ago
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(DevId,ProjectId,Activity,Description,AdditionalDescription,Hour,Day,Month,Year,WeekStartDate,Date,Estimated)";
Query += "VALUES('" + DeveloperId + "','" + ProjectID + "','" + ActivityId + "',";
Query += "'" + MVtxt.Text + "','" + txtAdditionalDesc.Text + "','" + MVddl.SelectedValue.ToString() + "','" + WeekDay + "','" + Month + "','" + Year + "',";
Query += "'" + StartDate + "','" + InsertDate + "','" + cbestimate.Checked + "')";
Value.Insert(Query);
0
Iftikar Hussain13y ago
Hi,
Please provide your query details

Regards,
Iftikar
0
Sanjeeb Lenka13y ago
plz share you Sql Query. so we can solve your problem.
0