Tip OleDbException on regional date/time formats

[siddhartha]

Using OLE and Jet, you may receive errors when saving system dates and times to databases in different regions.

Consider what happens when saving dates and times returned using the Now() function in Canada or the US: the date string "#2010/02/03 08:59 AM#" will save fine. If using 24 hour time, the string "#2010/02/03 08:59#" will also save fine.

If you change your system region to Spanish (Peru) or Spanish (Mexico), the ante meridiem changes from "AM" to "a.m." (or "p.m."). The date string becomes: "#2010/02/03 08:59 a.m.#"

Note that this will NOT save correctly to the database and throw a System.Data.OleDb.OleDbException. The workaround we use is:

Public Function clean_date(ByVal strDate As String) As String
  Dim str_buffer As String

  str_buffer = strDate
  str_buffer = Replace(str_buffer, "a.m.", "AM")
  str_buffer = Replace(str_buffer, "p.m.", "PM")
  str_buffer = Replace(str_buffer, "A.M.", "AM")
  str_buffer = Replace(str_buffer, "P.M.", "PM")

  Return str_buffer
End Function

 

[JohnH]

Using OLE and Jet, you may receive errors when saving system dates and times to databases in different regions.

That will only be a problem if you convert the Date value to string, the Date value itself is culture independent. Converting a Date value to String is something you only do for display purposes, not when storing the value in a database. In database you save the Date value. With ADO.Net you provide the Date value to a Command object (OleDbCommand) and similar using a parameterized query where in query a parameter is added and to command object the Parameters collection is used to add a Parameter object (OleDbParameter) and a value to this. SqlCommand.Parameters Property (System.Data.SqlClient) Link is to SqlCommand.Parameters help topic because is has a very simple code sample, but the all the data providers have this same functionality.

The date value does have some specific format when stored in db, but that is not your problem, in your application you only need to concern about Date values and pass these properly to the ADO.Net objects, which will hand them over correctly to the database. As for different cultures display format for dates, you can use the different To...String functions and they will display correctly according to culture. Also when you need to convert display strings in client app to Date values the functions are culture aware.