First, it's worth pointing out that the ONLY data type within Access that is appropriate for storing dates and times is the DATETIME data type. It is not uncommon to find that people are trying to work with dates and times stored in TEXT fields. The problem with this approach is that any sorting on dates in TEXT fields will only be done alphabetically because they will be treated as strings.
The Access DATETIME is actually an OLE Automation Date datatype. It is implemented as a double-precision (64bits) floating-point number. The time and date at the moment I start to type this is 08:34:27 on December 13th, 2008. As an OLE Automation Date, this appears as 39795.3572615509. The integer portion to the left hand side of the decimal point represents the number of days since December 31st 1899, while the the proportion of the current day in seconds that has elapsed since midnight appears after the point. 8.00am is one third of the day, or 0.33 of a day. At midday exactly, the OLE Automation Date will be 39795.5. You will also see this behaviour with Excel. For example, if you type 13/12/2008 into a cell, then select the cell directly below and press Ctrl + ' (the combination that copies the contents of the cell directly above) you will get 39795, unless the new cell has been formatted to a specific Date and Time format.
Inserting the current Date into Access can be done in a number of ways. The first example shows how to do this using the .Net DateTime data type directly:
string connect = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=|DataDirectory|mbbase.mdb";
using (OleDbConnection cn = new OleDbConnection(connect))
{
string sql = "INSERT INTO TEST (MyDateTime) VALUES (?)";
OleDbCommand cmd = new OleDbCommand(sql, cn);
cmd.Parameters.AddWithValue("", DateTime.Now.Date);
cn.Open();
cmd.ExecuteNonQuery();
}
This results in 13/12/2008 appearing in my copy of Access with the setting set on my machine as UK dd/mm/yyyy.
Probably the easiest way, however, with the current date is to use the built-in Date() function within Access and forget all about a parameter. The Date() function gets the current system date:
string connect = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=|DataDirectory|mbbase.mdb";
using (OleDbConnection cn = new OleDbConnection(connect))
{
string sql = "INSERT INTO TEST (MyDateTime) VALUES (Date())";
OleDbCommand cmd = new OleDbCommand(sql, cn);
cn.Open();
cmd.ExecuteNonQuery();
}
The Jet Provider is pretty good at parsing strings into Ole Automation Dates, so long as the string follows the format of a recognisable Date. I'll add a TextBox and a Button to a form, and use that as the source of the value that will be entered:
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server" />
<br />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
</div>
</form>
Next, I'll change the code-behind to put the processing in the button click event:
protected void Button1_Click(object sender, EventArgs e)
{
string connect = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=|DataDirectory|mbbase.mdb";
using (OleDbConnection cn = new OleDbConnection(connect))
{
string sql = "INSERT INTO TEST (MyDateTime) VALUES (?)";
OleDbCommand cmd = new OleDbCommand(sql, cn);
cmd.Parameters.AddWithValue("", TextBox1.Text);
cn.Open();
cmd.ExecuteNonQuery();
}
}
Using this set up, all of the following strings can be entered into the TextBox and inserted into the database DATETIME field without problem:
13 Dec 2008
13 December 2008
13 Dec 08
13 December 08
Dec 13 08
Dec 13 2008
13/12/2008
13/12/08
13-12-08
13 12 08
13 12 2008
Along with some others, but that should do for the time being.
The question is how do you get your users to enter the date in an acceptable format? There are a number of UI ways in which this can be done. You can tell them, by adding instructions to the form, or you can simply use a Calendar control (Calendar Extender from the Ajax Control Toolkit is good, as are many of the jQuery date picker plug ins) and set the format yourself. Or you can provide dropdown lists for day, month and year, and control the format that way.
The Time part of a DateTime is a tiny bit different. For example trying to pass in DateTime.Now will result in a "Data type mismatch in criteria expression" error. The reason for this is that the .Net DateTime.Now property includes milliseconds which can be seen by writing the current time using the following format:
String.Format("{0:yyyy-MM-dd hh:mm:ssss:ffffff}", DateTime.Now)
Access has no way of handling milliseconds, because if we remember, the right hand side of the OLE Automation Date only holds the proportion of the elapsed day in whole seconds. For the current time, the simplest solution is to use the Access built-in function Now() in your SQL, just as we did with Date() earlier. If you wanted to add another time, there are various options. The ToString() method removes the milliseconds from a .Net DateTime object, so this is one way to approach it:
protected void Button1_Click(object sender, EventArgs e)
{
string connect = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=|DataDirectory|mbbase.mdb";
using (OleDbConnection cn = new OleDbConnection(connect))
{
string sql = "INSERT INTO TEST (MyDateTime) VALUES (?)";
OleDbCommand cmd = new OleDbCommand(sql, cn);
cmd.Parameters.AddWithValue("", DateTime.Now.AddDays(-10).ToString());
cn.Open();
cmd.ExecuteNonQuery();
}
}
Another is to use the ToOADate() method, which converts the .Net DateTime to an Ole Automation Date, which as we have established is what Access really likes:
protected void Button1_Click(object sender, EventArgs e)
{
string connect = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=|DataDirectory|mbbase.mdb";
using (OleDbConnection cn = new OleDbConnection(connect))
{
string sql = "INSERT INTO TEST (MyDateTime) VALUES (?)";
OleDbCommand cmd = new OleDbCommand(sql, cn);
cmd.Parameters.AddWithValue("", DateTime.Now.AddHours(36).ToOADate());
cn.Open();
cmd.ExecuteNonQuery();
}
}
Again, there are a fair number of valid formats for times entered as strings into the TextBox:
13/12/2008 20:00:00
December 13 2008 8:00:00 PM
13 Dec 08 08:00:00
2008-12-13 18:00:00
etc.
Regional Settings
Generally, you will have the Access database on the same machine as the web application is running on, so there is unlikely to be any variation in terms of date formats. However, it is worth pointing out that the built-in Access functions, Date() and Now() pick up the current system time from the machine on which the database resides. Consequently, it come be a problem developing an application on a machine with UK Regional settings, where the date format is generally dd/mm/yyyy, and then transferring this to a US-based server, where the regional settings will expect mm/dd/yyyy. Changing the format in the Table Designer within Access has absolutely no effect on the underlying values. As we have already established, the underlying values are stored as double-precision floating-point numbers, not as formatted dates and times. The formatting option only allows you to instruct Access as to how you would like to see dates and times in Table view, or in Forms and Reports.
If you think there is any chance that your application may run on a machine where you have no control over the Regional Settings, or they are unknown, you are always best advised to input dates in the format yyyy-mm-dd, or ToOADate(). This way, there is no likelihood of the 3rd of July becoming the 7th of March at some stage in the future. Certainly when querying dates within Access, it is always best to use the yyyy-mm-dd format. In addition, using parameters eliminates most problems encountered when using delimiters with dates and times.
