1

I am working with DateTime objects to create a monitoring application. The principle is that each time a user launches a specific application, the current date and time will be saved in the DB. Then in the monitoring app, I get this date field from DB and display it.

As I went through testing today, I tried to launch the app : DateTime.Now is stored (ie, in French culture, 22/04/11 17:09:50 ). When I launch the monitoring app, what I get from the database is 22/04/11 00:00:00 Is there any reason why the exact time isn't saved?

The column in DB is, of course, a Date column.

Some code:

Storing the date:

                command = new MySqlCommand(_updateDateCommand, connection);
                command.Parameters.AddWithValue("?Template", app);
                command.Parameters.AddWithValue("?Date", DateTime.Now);
                command.Parameters.AddWithValue("?Id", u.Id);


                if (command.ExecuteNonQuery() != 0) return true;

Retrieving the data:

                object date = reader.GetValue(4);
                if (date == System.DBNull.Value)
                {
                    tempUserApp.DateLastUsed = DateTime.MinValue;
                }
                else
                {
                    tempUserApp.DateLastUsed = (DateTime)date;
                }

I'm sure that the problem comes from the storing process, because visualizing data with a tool such as Toad show me 22/04/11 00:00:00 in the Date field.

Update command:

private readonly string _updateDateCommand =
            "UPDATE userapplications " +
            "JOIN template t ON t.Name = ?Template " +
            "SET DateUsed=?Date  WHERE `User Id`=?Id AND `Template Id`=t.Id";

Is there any special formatting for MySQL dates?

I bumped into this StackOverflow question and tried the solution proposed, it does not work either

Any idea?

Community
  • 1
  • 1
Damascus
  • 6,553
  • 5
  • 39
  • 53

2 Answers2

2

The DB Column should be DateTime, not Date. Look at the Docs.

About DATE:

The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

About DATETIME:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

Getting the Date return like dd/mm/yyyy 00:00:00 is misleading, it's just being formatted by the tool as the default date/datetime format. What you really need is to set the DB column as DateTime.

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
0

Check the data type in your DataBase, it seems to me your field is a Date type, so

chaudcommeunnem
  • 1