MS Access Date and Time with ASP.NET

Getting odd results and unexpected errors when working with Dates and Times in Access through ASP.NET is a common problem. Here's an overview of the Access DateTime data type, and some resolutions to the more frequently encountered errors.

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.

 

Date Posted:
Last Updated:
Posted by:
Total Views to date: 48443

6 Comments

- Werner h

Thank you, thank you! and THANK YOU!
Been trying to figure this one out for a couple of hours, getting errors doing a simple update of a DateTime field in an Access db using a DateTime variable in C#.
Found lots of useless posts, lots of people still generating SQL statements by concatenating strings, etc. and finally stumbled on your post, and the mention of the OADate() function.
Great stuff! And great background info as well...
So thanks again, you made my day.

Live long & prosper!
Werner.

- Gustav Brock

> Access has no way of handling milliseconds ..

It has, but custom code and functions are needed:

http://www.devx.com/dbzone/Article/39046/1954

/gustav

- Mike

@Gustav

Accesswithin the context of an ASP.NET application has no way of handling milliseconds. It's actually a Jet database when the data file is divorced from an Access application. As such, you cannot invoke modules without using Office Interop, but then you need to install Office on the web server, which is totally outside the scope of this article.

- sanjay

thanks a lot

- Jasper

Can you make a Select * Where date="DATETIME"

Example here?

Because My code is creating a mismatch datatype criteria error

Appreciate your help

- newboys2013

Thank you for your super site in internet.
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and I end up deleting quite a lot. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Requests to fix your code (post a question to forums.asp.net instead, please)
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Lorenzo 3/26/2015 8:21 AM
In response to iTextSharp - Introducing Tables
Hi Mike How can I add padding to all cells in the table? Kind Regards Lorenzo...

Satyabrata Mohapatra 3/25/2015 8:11 AM
In response to How To Send Email In ASP.NET MVC
Great article. Simple and up to the point....

Afzaal Ahmad Zeeshan 3/24/2015 8:17 PM
In response to How To Send Email In ASP.NET MVC
A great way to teach the MVC framework for sending the emails... Also, what I found helpful was the...

Jim H 3/24/2015 2:32 PM
In response to Migrating From Razor Web Pages To ASP.NET MVC 5 - Model Binding And Forms
Thank you. This helps....

wazz 3/22/2015 5:48 AM
In response to Posting Data With jQuery AJAX In ASP.NET Razor Web Pages
great info!!...

rael 3/21/2015 8:53 PM
In response to Getting the identity of the most recently added record
I spent hours trying to figure how to achieve this in C#. This article helped me. Thanks a lot...

Stephen 3/21/2015 8:48 PM
In response to Ajax with Classic ASP using jQuery
This was very helpful, thanks:)...

patrick voes 3/19/2015 10:19 AM
In response to iTextSharp - Introducing Tables
Thank you! very helpfull....

Bigmachini 3/19/2015 6:13 AM
In response to ASP.NET MVC DropDownLists - Multiple Selection and Enum Support
This just made my day, afternoon, evening, night... was looking for a way of doing this without to a...

Bobbyg 3/19/2015 4:14 AM
In response to HTML Helpers For Forms In Razor Web Pages
Nice article. There are bugs in MVC 5 with dropdowns retaining values from other screens and them. I...