HP 3000 Date Conversion

  • Thread starter Thread starter Anthony Viscomi
  • Start date Start date
A

Anthony Viscomi

Hi all:
I am importing a text file which is a "dump" from a HP 3000; 2 of the
fields within the file are date fields. Currently this fields display as a 5
digit integer. My question is:

How do I convert these values to a recognizable date within MS Access?

Thanks in advance!

Anthony Viscomi
 
Changing the field type doesn't seem to work.

Example:
HP3000 date value 11239 is displayed 10/8/1930 when I change the field type.
BerHav said:
Hi,

Sounds like that the date is converted into a number during the export out
Click to expand...
of HP3000. Import your file as normal and change the field type of both date
fields to date/time this should fix it. Day 1 = 01/01/1900 if i remember
right => 08 July 2004 = 38176. Decimals would represent the hours, minutes
and seconds.
Probably you can create an import specification or import into a temporary
Click to expand...
table (or help table) and use an append query to transfer your data into
another table which is already formated, etc.
 
Anthony,

Unfortunately I don't know the HP3000.
What would you expect to get for 11239 as date? As Oct/08/1930 is what i would expect.

Probably you will have to add a small formula to get it calculated the right way.
 
The date should represent a Paid Date,thus 10/8/1930 wouldn't be valid since
we weren't in business back then. You're correct; I do need a formula.
 
step 1: find out what todays date looks like on the
HP3000 (i.e. what 5 digit number represents today)

step 2: find out what todays date looks like in access or
excel.

step 3: calculate the difference between the numbers in
1&2 above. this becomes the value you have to add in your
date conversion formula.

Access and excel store dates as 5 digit numbers where day
1 is jan 1, 1900 (sometimes 1904). feeding the proper
5digit number to an access date/time field will display as
a valid date. you just need to figure out what that number
is. the calculation in step 3 should help.

As an example, the database that I have to convert from
stores dates as 7-digit numbers where day 1 is Jan 1,
0001. To convert that number to a form useable in access,
I have to subtract 693596 from the stored date. This works
every time.

As noted in another reply, decimal places should represent
the time as a fraction 0.5 would be 12:00 noon. Access
will handle this conversion automatically.
 
Back
Top