Conversion of varchar datatype to a datetime resultant in an out renge value- Sql Server 2008 - Update

[Paramu1973]

I have problem. I can' identify my mistake... int dt = Convert.ToInt32(Items.Rows[T1]["F14"].ToString().Trim()); int mn = Convert.ToInt32(Items.Rows[T1]["F15"].ToString().Trim()); int yr= Convert.ToInt32(Items.Rows[T1]["F16"].ToString().Trim()); string DtString =mn.ToString().Trim() +"/"+ dt.ToString().Trim()+"/"+ yr.ToString().Trim(); DateTime RegExp = Convert.ToDateTime(DtString); exp_date is datetime field in sqlserver. string MyDtQry = "UPDATE MyTable SET exp_date='" + RegExp + "' where MyTable.id_no='" + AlmIDNo + "'"; I can't Identify the mistake Thanks For Ideas:rose:

[Not Active]

You have two mistakes The first is not properly formatting your code as per the posting guidelines. The second is not saying what errors or difficulties you are encountering.

---

I know the language. I've read a book. - _Madmatt

[Lost User]

Paramu1973 wrote:

string MyDtQry = "UPDATE MyTable SET exp_date='" + RegExp + "' where MyTable.id_no='" + AlmIDNo + "'";

In the above statement you need to check what value is being returned by RegExp and that it is in the correct format for both the SQL statement, and your regional date settings. I have a feeling that there are better ways to do this by using SQL parameters which accept DateTimes as values rather than strings.

The best things in life are not things.

[Dan Mos]

why not use the ctor for DateTime(int year, int month, int day)? Somthing like:

DateTime dt = new DateTime(yr,mm,dd)

Note that the code above, is written directly in here. Not sure if it will compile but I know that there is such a way to create a DateTime instance. :) [add] Also add a parameter. Don't pass the datetime as string(varchar in SQL using '') if the field is datetime in sql. The same for ID. Don't pass it as string/varchar type if it's int/long. Something like this:

string MyDtQry = "UPDATE MyTable SET exp_date=@dt where MyTable.id_no=@id";

cmd.Parameters.AddWithVAlue("@dt", RegExp);
cmd.Parameters.AddWithVAlue("@id", AlmIDNo);
//where cmd is sql command object

All the best, Dan

modified on Thursday, July 7, 2011 11:55 AM

[dasblinkenlight]

1. Instead of going through strings to construct an instance of DateTime, you should use the corresponding constructor: new DateTime(yr, mn, dt) 2. Instead of building a SQL string with embedded values, use parameterized SQL[^]: it makes your code more robust, not to mention considerable performance gains.

[PIEBALDconsult]

What datatypes are in the table fields? If they're Int32, just cast them. Don't convert to string and right back again. And don't use the Convert class, it's needless, just use the methods of the datatypes Int32.Parse etc. You can only pass a DateTime value direectly to SQL with a parameter (which is the right way to do it anyway).

[realJSOP]

Try it this way:

DateTime RegExp = new DateTime(0);
try
{
RegExp = new DateTime(yr, mn, dt);
// or
string dateStr = string.Format("{0}/{1}/{2}", yr, mn, dt);
bool parsed = DateTime.TryParse(dateStr, out RegExp);
if (parsed)
{
// success - do something else
}
else
{
error
}
}
catch (Exception ex)
{
}

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass." - Dale Earnhardt, 1997