Well, I guess, I spoke too soon. Jay's solution does work. And correctly too. But, I am still stuck with another problem
associated with this. It seems that the Dataflex database engine does store the date as a number of days in the database file
and that the basedate is: 01/01/1900 = 36535. But, the problem I have encountered now is it appears that the number is stored
in BCD format. ( this info was found on Data Access's newsgroup archive).
For instance at the address where the date is stored this is what appears: 83 01 00 00 01
That is supposed to be: 02/08/1999 once it is converted.
I am going to have to do more research on BCD format and how those numbers would appear in a HEX file. Again, thanks for your
help and suggestions.
james

"Cor Ligthert" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
> James,
>
> I real doubt it, althouhg not in Jays solution however.
>
> Dim dd As New DateTime(1799, 12, 31)
> dd = dd.AddDays(36525)
> MessageBox.Show(dd.ToString)
> = 01 januari 1900
>
> :-)
>
> Cor
>
>

Jay, as I stated in a response to Cor, it turns out from some more research that the Number of Days is stored in BCD format.
Here is a quote from one of Data Access's main designers that wrote the DF3.1d
file system:

"It is not the number of days since 01/01/1900 but since 00/00/0000.
It does not depend on 2 or 4 digit year format. It is ALWAYS the number of days since the start of counting. If the number is
LOWER than 36525 it can be _seen_ as a date since 1900.
The dates are stored as number and numbers are stored in BCD format. "

I had got the first bit of info from another post in Data Access's archive that stated that:
"The dates are stored as number of days since 01/01/1900 and that day is 36525 ."
And the other response I quoted contradicts that.
I really do appreciate your help.
As for adding one day or one month, to 00/00/0000, I am not able to test that. The original application uses the system date to
set the date for entries. And I don't have access to the original Dataflex code to see how that is coded. (this is an old DOS
application) I am building a new application in VB.NET that uses an Access Database to store the data. It consists of 63
individual tables ( in the original app. each table is in a seperate file). I need to be able to read the original Database
file and import it into Access.
I have been fairly successful in reading the files and finding what data is stored where. This particular version's header info
has changed some from the specs I found at a File Extension website. But, I am slowly finding the common addresses in the
headers of all the table files I am working with.
And reading Data Access's newsgroup archives has helped too. It seems that Y2K created some problems just about the time the
database file version I am working with was introduced. Even though the company considers the header formatting and other
methods proprietary, they did post some info to their developers that has given me some clues(like the BCD info on date storage)
that has helped.

Here is how the date: 02/08/1999 is represented in one file: 83 01 73 00 00 01 ( HEX values from Hex Editor)
I don't know if that is similar to other data in the file where the Least significant byte is first: ( 01 00 00 73 01 83) or
not. When I convert that to Decimal I get a HUGE number that seems to make no sense at all.
But, considering it represents BCD formatting, it may be correct. Anyway, I am certainly learning a lot of new things!!
Thanks for all the help and information you and Cor have given me.
james





"Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message news:OJ%(E-Mail Removed)...
> Question for James: I'm curious What date do you have when you add 1 day to 00/00/0000? What date do you have when you add 1
> month? Is year 0000 a Leap Year? a Leap Century?
>
> Based on the two functions I gave, it would seem that James's "00/00/0000" date is really 12/31/1799.
>
> In which case James could simplify the functions I gave earlier to:
>
> Private Const BaseDate As DateTime = #12/31/1799#
>
> Public Function ToFileDateTime(ByVal value As DateTime) As Integer
> Dim ts As TimeSpan = value.Date.Subtract(BaseDate)
> Return CInt(ts.TotalDays)
> End Function
>
> Public Function FromFileDateTime(ByVal days As Integer) As DateTime
> Return BaseDate.AddDays(days)
> End Function
>
> Which might be easier to follow, as you are simply finding the number of days between the two dates...
>
> Now my concern on James' function is Y2K, was it originally coded to account for the various leap years, centuries & what have
> you? By my routine 12/31/2005 is 74905? What does James' original code consider 12/31/2005 to be??
>
> Hope this helps
> Jay
>
>
> "Cor Ligthert" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>> James,
>>
>> Never done, however I think I would first bring it back to the date in ticks at 00:00:00 hour and than use the normal
>> datetime format.
>>
>> http://msdn.microsoft.com/library/de...tickstopic.asp
>>
>> I hope this helps something
>>
>> Cor
>>
>
>

James,

I dont believe that it is BCD (00) represents no character in BCD however a
nice puzzle for a newsgroup.

Why don't you give 10 dates with corresponding hex values, than *maybe*
somebody sees it.

Cor

Cor, thanks for the idea. I think I made a mistake on which values belonged to the BCD values for the date and included data
from the TIME value (that is stored in minutes that is not BCD.......thankfully).
According to a File List printout that I managed to get for my DAT files, the DATE field has a size of 6.
And the same for the TIME field. Then it hit me that the printout may have meant that the Date was 6 bytes long and each
address was holding 2 bytes (Hex values). That makes more sense to me.
Anyway, here are a few of the dates and their corresponding HEX values:

01/08/1999 -- 83 01 73
03/09/1999 --83 02 02
02/10/1998 --82 98 10
04/21/1998 -- 82 98 80
03/19/1999 --83 02 12
04/22/1999 --82 98 81
11/19/1997 --82 97 27


Hopefully, someone can make more sense of this than I have been able to do. I have been reading up on BCD encoding and have not
quite got the full meaning of it yet. As I understand it from the information that I could find at Dataflex's archive, the date
is changed into Number of DAYS, using 00/00/0000 as the starting date (according to one of Dataflex's main developers) and then
the number of days is converted to Binary Coded Decimal and that is what gets stored in the actual database file.
I am using a Hex editor to read the file(s) to get the values for each date as it appears in the data table.
Most of the data, looking at it in the Hex editor is actually Text. I have even loaded it into a RichText box and all the data
that is supposed to be ASCII text is displayed properly. Everything else is odd characters that have to be read and converted
differently.
Anyway, this is turning into quite a learning experience. As I said in a previous post, I have certainly gained a lot of respect
for those programmers that write database conversion tools. Especially those that preserve all the data types and field info.
james






"Cor Ligthert" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> James,
>
> I dont believe that it is BCD (00) represents no character in BCD however a nice puzzle for a newsgroup.
>
> Why don't you give 10 dates with corresponding hex values, than *maybe* somebody sees it.
>
> Cor
>

james,
I would start by using the routine to convert the date into the days # (from
the earlier post).

Then I would compare those values to the ones you list.

Unfortunately I don't see anything that is even close, in the bytes you
give.

Which means that my routine may be "off"... (the Base Date or Base Days
constants).

Looking at the numbers you give:

> 02/10/1998 --82 98 10
> 04/21/1998 -- 82 98 80

There are 70 days between these two dates, there is a difference of 70
between the 3rd byte, this would suggest the 3rd is the least significant
digit. I would then expect the first byte to be the most significant digit
(by examining the entire column).

> 04/21/1998 -- 82 98 80
> 04/22/1999 --82 98 81

Unfortunately these two are not a day apart, instead they are a year apart!


Meaning:
> 01/08/1999 -- 83 01 73
days = 830173
> 03/09/1999 --83 02 02
days = 830202
> 02/10/1998 --82 98 10
days = 839110
> 04/21/1998 -- 82 98 80
days = 829880
> 03/19/1999 --83 02 12
days = 830212
> 04/22/1999 --82 98 81
days = 829881
> 11/19/1997 --82 97 27
days = 829727

Things still don't line up. Given a function like:

Private Class Bucket
Public ReadOnly theDate As Date
Public ReadOnly theDays As Integer

Public Sub New(ByVal theDate As Date, ByVal theBytes As String)
Me.theDate = theDate
Me.theDays = CInt(theBytes.Replace(" ", ""))
End Sub

Public Function SubtractDays(ByVal other As Bucket) As Integer
Return theDays - other.theDays
End Function

Public Function SubtractDates(ByVal other As Bucket) As Integer
Dim ts As TimeSpan = theDate.Subtract(other.theDate)
Return CInt(ts.TotalDays)
End Function

End Class

Dim list As New ArrayList

list.Add(New Bucket(#11/19/1997#, "82 97 27"))

list.Add(New Bucket(#2/10/1998#, "82 98 10"))
list.Add(New Bucket(#4/21/1998#, "82 98 80"))

list.Add(New Bucket(#1/8/1999#, "83 01 73"))
list.Add(New Bucket(#3/9/1999#, "83 02 02"))
list.Add(New Bucket(#3/19/1999#, "83 02 12"))
list.Add(New Bucket(#4/22/1999#, "82 98 81"))


For Each bucket1 As Bucket In list
For Each bucket2 As Bucket In list
If bucket2.theDate <> bucket1.theDate Then
Dim days1 As Integer = bucket2.SubtractDates(bucket1)
Dim days2 As Integer = bucket2.SubtractDays(bucket1)
If days1 <> days2 Then
Debug.WriteLine(bucket2.theDate.ToShortDateString(),
bucket1.theDate.ToShortDateString())
Debug.Indent()
Debug.WriteLine(days1, "date diff")
Debug.WriteLine(days2, "days diff")
Debug.WriteLine(days2 - days1, "diff diff")
Debug.Unindent()
End If
End If
Next
Next

Shows a number of your this new days number are off by either a month or a
year. A couple appear to be off by 13 months...

Hopefully I've given you some ideas to go on.

Hope this helps
Jay


"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message
news:O0ib5H$(E-Mail Removed)...
> Cor, thanks for the idea. I think I made a mistake on which values
> belonged to the BCD values for the date and included data from the TIME
> value (that is stored in minutes that is not BCD.......thankfully).
> According to a File List printout that I managed to get for my DAT files,
> the DATE field has a size of 6.
> And the same for the TIME field. Then it hit me that the printout may
> have meant that the Date was 6 bytes long and each address was holding 2
> bytes (Hex values). That makes more sense to me.
> Anyway, here are a few of the dates and their corresponding HEX values:
>
> 01/08/1999 -- 83 01 73
> 03/09/1999 --83 02 02
> 02/10/1998 --82 98 10
> 04/21/1998 -- 82 98 80
> 03/19/1999 --83 02 12
> 04/22/1999 --82 98 81
> 11/19/1997 --82 97 27
>
>
> Hopefully, someone can make more sense of this than I have been able to
> do. I have been reading up on BCD encoding and have not quite got the
> full meaning of it yet. As I understand it from the information that I
> could find at Dataflex's archive, the date is changed into Number of DAYS,
> using 00/00/0000 as the starting date (according to one of Dataflex's main
> developers) and then the number of days is converted to Binary Coded
> Decimal and that is what gets stored in the actual database file.
> I am using a Hex editor to read the file(s) to get the values for each
> date as it appears in the data table.
> Most of the data, looking at it in the Hex editor is actually Text. I
> have even loaded it into a RichText box and all the data that is supposed
> to be ASCII text is displayed properly. Everything else is odd characters
> that have to be read and converted differently.
> Anyway, this is turning into quite a learning experience. As I said in a
> previous post, I have certainly gained a lot of respect for those
> programmers that write database conversion tools. Especially those that
> preserve all the data types and field info.
> james
>
>
>
>
>
>
> "Cor Ligthert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> James,
>>
>> I dont believe that it is BCD (00) represents no character in BCD however
>> a nice puzzle for a newsgroup.
>>
>> Why don't you give 10 dates with corresponding hex values, than *maybe*
>> somebody sees it.
>>
>> Cor
>>
>
>

Thanks for the ideas Jay. One quick question. As I mentioned in my previous post, the Dataflex developers all state that the
database engine converts the date to number of days then to BCD before storing it in the database. Could that be the reason
for the Hex values being so far off ? It seems to me that they are doing more .
<pause in thinking>

I just tried something, I took the Hex Values like:
830173 ( 83 01 73) and divided them by 365 and all of the values come out to: 2274.4465753424657534246575342466 ( 02/08/1999)
OR
2274.526027397260273972602739726
for Hex value: 83 02 02 ( 03/09/1999)
OR
2280.0356164383561643835616438356
for Hex value ( 83 22 13) (09/09/2004)

I doubt if that is significant , but, it did seem kind of odd.
I will continue to work with the suggestions you have provided Jay and will let you know what progress, if any, I make.
Thanks again for all your help.
james


"Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> james,
> I would start by using the routine to convert the date into the days # (from the earlier post).
>
> Then I would compare those values to the ones you list.
>
> Unfortunately I don't see anything that is even close, in the bytes you give.
>
> Which means that my routine may be "off"... (the Base Date or Base Days constants).
>
> Looking at the numbers you give:
>
>> 02/10/1998 --82 98 10
>> 04/21/1998 -- 82 98 80
>
> There are 70 days between these two dates, there is a difference of 70 between the 3rd byte, this would suggest the 3rd is the
> least significant digit. I would then expect the first byte to be the most significant digit (by examining the entire column).
>
>> 04/21/1998 -- 82 98 80
>> 04/22/1999 --82 98 81
>
> Unfortunately these two are not a day apart, instead they are a year apart!
>
>
> Meaning:
>> 01/08/1999 -- 83 01 73
> days = 830173
>> 03/09/1999 --83 02 02
> days = 830202
>> 02/10/1998 --82 98 10
> days = 839110
>> 04/21/1998 -- 82 98 80
> days = 829880
>> 03/19/1999 --83 02 12
> days = 830212
>> 04/22/1999 --82 98 81
> days = 829881
>> 11/19/1997 --82 97 27
> days = 829727
>
> Things still don't line up. Given a function like:
>
> Private Class Bucket
> Public ReadOnly theDate As Date
> Public ReadOnly theDays As Integer
>
> Public Sub New(ByVal theDate As Date, ByVal theBytes As String)
> Me.theDate = theDate
> Me.theDays = CInt(theBytes.Replace(" ", ""))
> End Sub
>
> Public Function SubtractDays(ByVal other As Bucket) As Integer
> Return theDays - other.theDays
> End Function
>
> Public Function SubtractDates(ByVal other As Bucket) As Integer
> Dim ts As TimeSpan = theDate.Subtract(other.theDate)
> Return CInt(ts.TotalDays)
> End Function
>
> End Class
>
> Dim list As New ArrayList
>
> list.Add(New Bucket(#11/19/1997#, "82 97 27"))
>
> list.Add(New Bucket(#2/10/1998#, "82 98 10"))
> list.Add(New Bucket(#4/21/1998#, "82 98 80"))
>
> list.Add(New Bucket(#1/8/1999#, "83 01 73"))
> list.Add(New Bucket(#3/9/1999#, "83 02 02"))
> list.Add(New Bucket(#3/19/1999#, "83 02 12"))
> list.Add(New Bucket(#4/22/1999#, "82 98 81"))
>
>
> For Each bucket1 As Bucket In list
> For Each bucket2 As Bucket In list
> If bucket2.theDate <> bucket1.theDate Then
> Dim days1 As Integer = bucket2.SubtractDates(bucket1)
> Dim days2 As Integer = bucket2.SubtractDays(bucket1)
> If days1 <> days2 Then
> Debug.WriteLine(bucket2.theDate.ToShortDateString(), bucket1.theDate.ToShortDateString())
> Debug.Indent()
> Debug.WriteLine(days1, "date diff")
> Debug.WriteLine(days2, "days diff")
> Debug.WriteLine(days2 - days1, "diff diff")
> Debug.Unindent()
> End If
> End If
> Next
> Next
>
> Shows a number of your this new days number are off by either a month or a year. A couple appear to be off by 13 months...
>
> Hopefully I've given you some ideas to go on.
>
> Hope this helps
> Jay

James,

I strugled a little bit, however what do you mean with BCD what means for me
Binary Coded Decimal however that is represented in 2 Hex digits and 6
positions is to small.

I searched on Internet and than I saw this, is it maybe this kind of packed
BCD as mentioned on this page.

http://www.danbbs.dk/~erikoest/bcd.htm

For that I am almost sure that Jay has a nice routine (while I don't know if
he has time for that of course).

However I don't know as well if that gives the answer.

Cor

James,
My understanding of BCD is that each hex digit (nibble) is its decimal
digit.

http://www.webopedia.com/TERM/B/BCD.html

A byte that contains &H83 is bcd for 83 decimal.

A byte that contains &H01 is bcd for 1 decimal.

A byte that contains &H73 is bcd for 73 decimal.

What I'm not sure about is what if anything they do with the sign (I would
not expect a date to have a sign).

Now when you have a sequence of bytes, endian makes a difference (which end
of the sequence is most significant). My initial test was taking the first
byte as most significant (&H83), it may be that the last byte (&H73) is most
significant. In both cases the numbers seem way too large or way too small
for the conversion you gave earlier.

I'm curious on how Dataflex actually encodes their BCD.

Hope this helps
Jay

"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message
news:(E-Mail Removed)...
> Thanks for the ideas Jay. One quick question. As I mentioned in my
> previous post, the Dataflex developers all state that the database engine
> converts the date to number of days then to BCD before storing it in the
> database. Could that be the reason for the Hex values being so far off ?
> It seems to me that they are doing more .
> <pause in thinking>
>
> I just tried something, I took the Hex Values like:
> 830173 ( 83 01 73) and divided them by 365 and all of the values come out
> to: 2274.4465753424657534246575342466 ( 02/08/1999)
> OR
> 2274.526027397260273972602739726
> for Hex value: 83 02 02 ( 03/09/1999)
> OR
> 2280.0356164383561643835616438356
> for Hex value ( 83 22 13) (09/09/2004)
>
> I doubt if that is significant , but, it did seem kind of odd.
> I will continue to work with the suggestions you have provided Jay and
> will let you know what progress, if any, I make.
> Thanks again for all your help.
> james
>
>
> "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> james,
>> I would start by using the routine to convert the date into the days #
>> (from the earlier post).
>>
>> Then I would compare those values to the ones you list.
>>
>> Unfortunately I don't see anything that is even close, in the bytes you
>> give.
>>
>> Which means that my routine may be "off"... (the Base Date or Base Days
>> constants).
>>
>> Looking at the numbers you give:
>>
>>> 02/10/1998 --82 98 10
>>> 04/21/1998 -- 82 98 80
>>
>> There are 70 days between these two dates, there is a difference of 70
>> between the 3rd byte, this would suggest the 3rd is the least significant
>> digit. I would then expect the first byte to be the most significant
>> digit (by examining the entire column).
>>
>>> 04/21/1998 -- 82 98 80
>>> 04/22/1999 --82 98 81
>>
>> Unfortunately these two are not a day apart, instead they are a year
>> apart!
>>
>>
>> Meaning:
>>> 01/08/1999 -- 83 01 73
>> days = 830173
>>> 03/09/1999 --83 02 02
>> days = 830202
>>> 02/10/1998 --82 98 10
>> days = 839110
>>> 04/21/1998 -- 82 98 80
>> days = 829880
>>> 03/19/1999 --83 02 12
>> days = 830212
>>> 04/22/1999 --82 98 81
>> days = 829881
>>> 11/19/1997 --82 97 27
>> days = 829727
>>
<<snip>>

Cor, I found that webpage too. Yes, I mean Binary Coded Decimal too. That is the info I found in Dataflex's Newsgroup Archives.
The 6 digit information is what I got from using a demo version of
Visual Dataflex 10 (time limited, fully functional development enviroment) that had a feature that would
print a complete list of all the individual tables and their associated fields, data types , and field sizes.
It gives each record's length for a given entry, current number of records (from the time of the printout) and maximum number of
records that each table (each table is in a single .DAT file by itself, unlike Access where a single .MDB file can contain
multiple tables).
But, unfortunately for me, it only gives the maximum length for each field and the DATE field(s) in each table all have a length
of 6. So, it is only 3 positions ( 2 Bytes per position) long. The 6 represents (3), 2 byte positions. It took me a bit to
figure that out as it has been a while since I had worked with Hex files. But, still even using that, the dates don't make
sense :
Like: 09/09/2004 = 83 22 13 . Which , using Jay's code, results in some huge date differences.
I have posted a message in one of Dataflex's newsgroups asking for more information, but, so far all I have received is people
offering to sell me an ODBC driver to use with ACCESS to connect to the Dataflex .DAT files or an offer to do the table
conversions for me.
I am hoping that one of the programmers there that has helped me in the past might see the post and respond with some more info
on the DATE conversion and File Header structure.
(I can always hope! )
Thanks for your help.
james

"Cor Ligthert" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> James,
>
> I strugled a little bit, however what do you mean with BCD what means for me Binary Coded Decimal however that is represented
> in 2 Hex digits and 6 positions is to small.
>
> I searched on Internet and than I saw this, is it maybe this kind of packed BCD as mentioned on this page.
>
> http://www.danbbs.dk/~erikoest/bcd.htm
>
> For that I am almost sure that Jay has a nice routine (while I don't know if he has time for that of course).
>
> However I don't know as well if that gives the answer.
>
> Cor
>

That too is how I understand the different explainations I have found on various websites and from what little I remember from
learning binary math years ago. (most of which I had forgotten and wish I had not and now am relearning!)
Here is an excerpt from an old post in their archives talking about Y2K problems and how one programmer was working with it:

"Yes, I know, but I need the same source code to be compilable for all these
versions. Currently, after doing a SYSDATE command, I add 693975 to the
result and it gives the right date. If dates in the year 2000+ get set up
as year 100, 101, etc., then I can simply continue doing this. If I set up
my 2.3b Enhanced systems to return a 4-digit year, I'll have to start
maintaining different source versions or modify the code after the SYSDATE
command to only add 693975 if the date returned is before 01/01/2000. I'm
hoping that the behavior I see with the year 100 is what is expected. Then
I can avoid modifying and deploying about 750 applications. "


In the old application that I am converting to Access db, it takes a two digit year ( 05 for 2005) if manually entering a date
or if the user just hits Enter, it enters the System Date (SYSDATE in Dataflex) which is the computer's current date. I posted
a request for more information on one of their current newsgroups that has helped me in the past. I am hoping that someone there
will be willing to share some more info on the DATE storage and a couple of other issues . As I told Cor in another post, so
far all I have got is offers to sell me an ODBC driver for Access to work with the Dataflex files($500.00) or
an offer to do the table conversions for me. (also quite expensive) Neither option is really what I want to do or can really
justify right now. Especially since my main hold up is the DATES . The rest of the problem (reading the Header structure for
Data Types and Field sizes) is about done. I have just had to do a search of several tables to see what addresses that contain
data info (not the actual data itself) are common to all the tables. I have had much better luck there. The rest of the data
besides the dates, is either in ASCII text or Decimal values that represent numbers, like, Number of Records, TIME (given in
seconds in the day) and several other bits of info. If I can find a way to get the dates correct, then I will have the biggest
part of the conversion proccess fixed.
Thanks so much for all your help. You and Cor have been a wealth of information.
james





"Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> James,
> My understanding of BCD is that each hex digit (nibble) is its decimal digit.
>
> http://www.webopedia.com/TERM/B/BCD.html
>
> A byte that contains &H83 is bcd for 83 decimal.
>
> A byte that contains &H01 is bcd for 1 decimal.
>
> A byte that contains &H73 is bcd for 73 decimal.
>
> What I'm not sure about is what if anything they do with the sign (I would not expect a date to have a sign).
>
> Now when you have a sequence of bytes, endian makes a difference (which end of the sequence is most significant). My initial
> test was taking the first byte as most significant (&H83), it may be that the last byte (&H73) is most significant. In both
> cases the numbers seem way too large or way too small for the conversion you gave earlier.
>
> I'm curious on how Dataflex actually encodes their BCD.
>
> Hope this helps
> Jay