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: Saturday, December 13, 2008 9:47 PM
Last Updated: Monday, September 28, 2009 7:09 AM
Posted by: Mikesdotnetting
Total Views to date: 42642

6 Comments

Sunday, September 27, 2009 2:00 PM - 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.

Thursday, May 13, 2010 2:24 PM - 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

Thursday, May 13, 2010 7:12 PM - 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.

Sunday, October 7, 2012 5:45 AM - sanjay

thanks a lot

Tuesday, February 26, 2013 1:25 PM - Jasper

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

Example here?

Because My code is creating a mismatch datatype criteria error

Appreciate your help

Monday, March 25, 2013 8:21 PM - 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 some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

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

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...

Chet Ripley 11/15/2014 6:57 PM
In response to Adding A New Field
It appears the command is case sensitive. I had the same issue as Cameron. When I changed the to it...

Alvin 11/14/2014 12:49 PM
In response to Razor Web Pages E-Commerce - Adding A Shopping Cart To The Bakery Template Site
Great article Mike! When do you plan to extend the bakery shopping cart beyond this point?...

Gautam 11/14/2014 10:16 AM
In response to Web Pages - Efficient Paging Without The WebGrid
to get the count can we use only the below sql, why to join category and author table var sql =...