.net DateTime object, ensuring MM/DD/YYYY formatting

2
votes

It seems that the .net DateTime object defaults to whatever format the computer is set to. As I'm from the UK, this means that the DateTime object is DD/MM/YYYY.

This, typically, is fine, but I'm trying to use the DateTime object to put the current time in on a record submission on an Access database. Access (or SQL in general, don't really know) requires that the date is formatted in a MM/DD/YYYY format.

Is there a way I can have the DateTime object just format the date in the MM/DD/YYYY format, or am I going to have to convert to string, switch the string around, and insert that?

4
.netsqlms-accessdatetime
You probably should go with yyyy-MM-dd format (ISO 8601) - Rubens Farias
Actually, if you're using DateTime fields correctly, it won't matter what format the date is in, because there is no conversion to string. You're not using concatenated sql strings, are you? That would be bad. - Erik Funkenbusch
Well, use parameterized queries and you won't have this problem. - Erik Funkenbusch

4 Answers

5
votes

If you are inserting into a database don't use a Date String. Use a Parameter Object and give the Value the date object.

for access

string mystatment = "Insert into TableName (column1, datecolumn) values (?,?)"

OleDbCommand cmd = conn.CreateCommand();
cmd.Commandtext = mystatement;
cmd.Parameters.AddWithValue("@column1", mycolumn1variable);
cmd.Parameters.AddWithValue("@datecolumn", yourDateTimeVariable);

if you are properly doing this with production code make sure you have exception handling etc.

using (OleDbConnection conn = new OleDbConnection("connection string"))
{
    using (OleDbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "Insert into TableName (column1, datecolumn) values (?,?)";

        OleDbParameter param;
        param = new OleDbParameter("name",  OleDbType.VarChar, 36); // if you know the size of the column specify it
        param.Value = mycolumn1variable;
        cmd.Parameters.Add(param);

        param = new OleDbParameter("mydate", OleDbType.Date); // if you know the size of the column specify it
        param.Value = yourDateTimeVariable;
        cmd.Parameters.Add(param);

        conn.Open();
        cmd.ExecuteNonQuery(); //etc
    }
}

for sql you use the named @parameters instead of the ? in your statement and use a SqlCommand instead.

If it's for screen display, repect the users settings and format based on their culture.

0
votes

You can call DateTime.ToString("MM/dd/yyyy") so you don't have to switch it around

0
votes

Use ToString method of DateTime structure:

using System;
using System.Globalization;

string s = DateTime.Now.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture);
Console.WriteLine(s);
0
votes

You can do something like this when you utilize the DateTime class:

DateTime yourDate = DateTime.Now;
Console.WriteLine("Your Date: {0}", yourDate.ToString("MM/dd/yyyy"));