In Microsoft SQL Server Management Studio, after calling the stored procedure, I can see that the time has the format 2011-05-20 19:56:09 in table.
However, in my C# program, after using an OdbcConnection to get the record from the table, I find that the time is in the format 05/20/2011 19:56:09. So I manually convert the format MM/DD/YYYY hh:mm:ss to YYYY-MM-DD hh:mm:ss (Actually, how do I verify the format is h or HH since the hour shown is 19?).
My question is why and in which part of connection, the format is changed? How can I set invarient culture in my connection?
cmd = new OdbcCommand("{?=CALL stored_procedure_in_SQL(?,?)}", m_SqlConn);
cmd.Parameters.Add(new OdbcParameter("RETURN_VALUE", OdbcType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0, null,
DataRowVersion.Default, null));
//@parameter1
cmd.Parameters.Add("@parameter1", OdbcType.VarChar, 50).Value = value1;
//@parameter2
cmd.Parameters.Add("@parameter2", OdbcType.Int)=value2;
object objTime=ds.Tables[0].Rows[0][0]
Console.WriteLine(objTime.Tostring()) //05/20/2011 19:56:09
DateTime dateTime = Cdate(objTime, "MM/dd/yyyy h:mm:ss tt");
//2011-05-20 19:56:09
Console.WriteLine(dateTime.ToString("yyyy-MM-dd HH:mm:ss"));
//Function Cdate
static public DateTime Cdate(object val, string format)
{
// check if the object is a date time alread
string str;
if(val is DateTime)
{
// Since val is already a dateTime,Return here
return (DateTime)val;
}
// convert via string
if(val != DBNull.Value)
{
str = val.ToString().Trim();
}
else
{
str = "";
}
if(str == "")
{
return DateTime.MinValue;
}
else
{
return DateTime.ParseExact(str, format,
CultureInfo.InvariantCulture);
}
}
DateTimebut the same kind of logic might be applied here).returnsinCDatemethod. Can you please just tell us which one is executed in your case?DateTimeis aDateTimeis aDateTime- it has no format - it's just aDateTime. What you see are string representations of yourDateTime- you can chose how you want yourDateTimeto be formatted, but that's on a query-by-query basis, or as a setting in your database - it's not something you can set / change in your connection string