How to convert an internal numeric date to Access Date/Time format?

Question

I get some data from a PICK/UniVerse database that includes dates in a 4 or 5 character numeric format. Here are some examples .. I grabbed the date values from the database, and compared it to the date being shown in an application:

9832       12/1/1994
10027      6/14/1995
10594      1/1/1997

Is it possible to convert these into something that can be put into Access as a Date/Time value?

As A test, I put 9832 in Excel as a General format and then change it to Short Date, it comes up as 12/1/1926. So it's off by exactly 68 years. This was true for 10027 and 10594 as well.

Answer 1

In C# you can use DateTime.FromOADate

DateTime dt = DateTime.FromOADate(41481);

Returns a DateTime equivalent to the specified OLE Automation Date.

That will give you:

dt = {26/07/2013 12:00:00 AM}

Later on you can insert that Date in your Access database.

Answer 2

A little late to this thread but I'll post some more detail: The Pick / MultiValue DBMS stores dates as an integer with date 0 = 12/31/1967. So as I write this on Jan 16, 2014 the internal Pick date is 16818. If you use the following you'll get that magic number 24837:

DateTime.Parse("12/31/1967").Subtract( DateTime.FromOADate(0)).Days

So add that to your Pick Date to get the OADate.

If you're using any of the common MV DBMS libraries for extracting data (UniObjects, U2.NET, mv.NET ...) you shouldn't need to convert the date like this. A typical function might look like:

string date = OConv( record["PurchaseDate"], "d2/" ); // "01/16/14"

Or rather than extracting the data in the internal DBMS format, you really should be getting it in external format to start. Ask the DBMS developer who provided the data to do this for you. It's real easy on their side to return " date'd2/' " rather than just "date".

Feel free to contact me directly if you need more info in this area.

Answer 3

Access Date/Time values are actually double precision floats. The whole number portion represents the day and the integer portion represents the time of day.

It looks like those Pick date numbers correspond directly to the date portions of Access Date/Time values. So you can use CDate to transform them.

? CDate(41481)
7/26/2013 

Experiment some more to get a feel for this:

? Date()
7/26/2013 
? CDbl(Date())
 41481 

Note, although your question is tagged with c#, you don't need that to do these conversions. You can do them with an Access query and ask the db engine to apply those functions.

Since it turned out those date numbers are consistently offset by 68 years, you can still do the conversion in an Access query.

? DateAdd("yyyy", 68, CDate(9832))
12/1/1994 
? DateAdd("yyyy", 68, CDate(10027))
6/14/1995 
? DateAdd("yyyy", 68, CDate(10594))
1/1/1997 

Or ...

? CDate(9832 + CLng(24837))
12/1/1994 
? CDate(10027 + CLng(24837))
6/14/1995 
? CDate(10594 + CLng(24837))
1/1/1997 

Answer 4

All multivalue database dates (this includes UniVerse and UniData) are based on a base date of 31st December 1967. You can resolve this to an external data in a number of ways.

The favourite - e.g. if using SQL or one of the internal database tools is to create a data dictionary entry for the field concerned with a date conversion field, For example:

'D2' for a 2-digit year 'D4' for a 4-digit year 'D4/' for a 4-digit year with slash separators 'D4/E' for a 4-digit year with slash separators and explicitly in European format (DD/MM/YYYY) as compared to US format (MM/DD/YYYY).

If no explicit formatting is given then the format will default to environmental settings. There are other formatting options as well and many can be used in combination (as with the above).

As previously advised, the alternative is to adjust the raw date with a formula. The date is in days since 31st December 1967 - The base data for all multivalue databases.