1

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.

Jeff Brady
  • 1,454
  • 7
  • 35
  • 56
  • Do you know how 41481 is related to 7/26/2013? Is it days since? Minutes since? Without that info it might be hard, with it it should be easy. – Toby Allen Jul 26 '13 at 18:30
  • I updated my question with actual date values taken from the database, and compared to the dates that show up in an application. – Jeff Brady Jul 26 '13 at 18:46

4 Answers4

3

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.

Habib
  • 219,104
  • 29
  • 407
  • 436
  • Thanks .. When I do that, the month and day are correct but the year is off by 68 years (ex: 1926 instead of 1994). It looks like I need to add 24837 to my values and then use DateTime.FromOADate to convert them and that should work. – Jeff Brady Jul 26 '13 at 18:57
  • @JeffBrady,You are welcome, strange it is off by 68 years, it worked for the value `41481` – Habib Jul 26 '13 at 18:59
  • 41481 wasn't actually in the database .. i was just using it as an example. I went back and updated my question with actual data. – Jeff Brady Jul 26 '13 at 19:01
  • @JeffBrady, I guess than adding `24837` should be the right way, I am not sure if there exists any direct way of converting that number to date. – Habib Jul 26 '13 at 19:13
1

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
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • thanks .. I'll check out doing this during the query. I'm doing an `INSERT` with 165 fields so it's already pretty messy :) – Jeff Brady Jul 26 '13 at 18:59
1

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.

TonyG
  • 1,432
  • 12
  • 31
0

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.

John Jenkins
  • 1