0

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);
    }
}
4
  • 3
    I doubt that the value has a format in the database. Check here for details on that (dealing with C# DateTime but the same kind of logic might be applied here). Commented May 30, 2011 at 20:54
  • You have too many returns in CDate method. Can you please just tell us which one is executed in your case? Commented May 30, 2011 at 20:55
  • @Snowbear, the first return is executed in my program, as the comment added Commented May 30, 2011 at 21:06
  • 1
    A DateTime is a DateTime is a DateTime - it has no format - it's just a DateTime. What you see are string representations of your DateTime - you can chose how you want your DateTime to 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 Commented May 30, 2011 at 21:06

3 Answers 3

9

You can't set a DateTime format on a connection string.

What you are seeing are simply different formattings of a certain (internal) representation of DateTime.

The formatting is determined by the tools you use and for .NET code the culture your logged in with.

When you want to display the time, then you need to format, though when using a custom format string like you have, there are no CultureInfo elements involved.

Check the different ToString overloads on DateTime.

Sign up to request clarification or add additional context in comments.

3 Comments

+1 exactly - a DateTime in SQL Server is just a DateTime - and doesn't have a "format" associated with it.
If I understand right, since my format is yyyy-MM-dd HH:mm:ss, which does not correspond to any format, I do not need to worry about CultureInfo when displaying?
@Summer - I suggest reading the documentation on custom Date and Time format strings. Culture may have an effect, though I don't believe that in this particular case there is one.
1

You can't set the dateformat in your connectionstring, but you can call SET DATEFORMAT DMY in the query batch.

Comments

-1

You CAN set the date and time format in the connection string using the DateTimeFormat parameter.

The documentation for it is a bit scant. But one reference to various options this parameter can have is at 'SQLiteConnection.ConnectionString Property'

In brief, the options are: DateTimeFormat

  • Ticks - Use the value of DateTime.Ticks.
  • ISO8601 - Use the ISO-8601 format. Uses the "yyyy-MM-dd HH:mm:ss.FFFFFFFK" format for UTC DateTime values and "yyyy-MM-dd HH:mm:ss.FFFFFFF" format for local DateTime values).
  • JulianDay - The interval of time in days and fractions of a day since January 1, 4713 BC.
  • UnixEpoch - The whole number of seconds since the Unix epoch (January 1, 1970).
  • InvariantCulture - Any culture-independent string value that the .NET Framework can interpret as a valid DateTime.
  • CurrentCulture - Any string value that the .NET Framework can interpret as a valid DateTime using the current culture.

An example usage:

string connectionString = "Data Source={0};Version=3;DateTimeFormat=InvariantCulture";

1 Comment

Why the downvote?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.