Access dataBase - Date\Time field issue

Question

Hello, I use the UPDATE statment in order to update a Date\Time field. i use the Date.Now.ToShortDateString() statement. when i run in debug mode i see that the value is for example "18/09/2010". then, when i open the Data Base i see that the value that was insert is "09/18/2010".

Does anyone know how can i fix it? (i later display this date field in my GUI so i need it to be in the right way)

Thanks,

Tamar

Answer 1

This is a culture problem of your application. Too bad as far as I know access doesn't support changing the culture of its database. Change the culture of the application to en-US or even better use UTC dates and convert them to the right culture in your application when you want to show them.

Answer 2

Use parameters instead of concatenating strings. For the date/time parameter, set the parameter type to date/time, and pass Date.Now rather than Date.Now.ToShortDateString().

Here's an example:

public class Program
{
    static void Main(string[] args)
    {
        OleDbConnection conn = 
           new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[put path to mdb here...]");

        conn.Open();

        OleDbCommand cmd = new OleDbCommand(
            "update Table1 set dateField = ? where textField like ?", 
            conn);
        cmd.Parameters.Add("@dateField", OleDbType.Date).Value = DateTime.Now;

        // IN THIS EXAMPLE, THE RECORD BEING UPDATED WILL HAVE "TEST"
        // IN THE FIELD NAMED: TEXTFIELD 
        cmd.Parameters.Add("@textField", OleDbType.VarChar).Value = "test";
        cmd.ExecuteNonQuery();

        conn.Close();

        Console.WriteLine();
        Console.WriteLine("Press enter to exit.");
        Console.ReadLine();
    }
}

When you open the MDB file you can confirm that the field has been changed to DateTime.Now.

Answer 3

The date format in Access is set by the locale in Windows, look at Control Panel, Region and Language Settings, unless you wish to set the format for individual fields via the format property of fields (columns) and controls.

When updating via forms and the query design window, you have some latitude, and in this case you are lucky that 18 is not a month, so most formats are unambiguous, however, in VBA and, I suspect, in c#, you should use either American date format (month, day, year) or an unamibiguous format such as year, month, day. i would generally prefer year, month, day over US dates, because they are clearer to more people.

Answer 4

Always pass dates in explicitly mentioning month part in long format. For example for 01/24/2010 pass it as 24/jan/2010. In this case MS access will not get confused about month part and will process the date successfully. Later, date displayed will be as expected by you. Please remember that is applies for data insertion, updating and date fetches also.It will work for all Cultures because MS Access need not to worry about which part is month part of date value passed