Date from Number of Days

  • Thread starter Thread starter james
  • Start date Start date
J

james

I have a problem that at first glance seems not that hard to figure out. But, so far, the answer has escaped me. I have an old
database file that has the date(s) stored in it as number of days.
An example is: 36,525 represents 01/01/1900. The starting point date is considered to be :
00/00/0000. I have looked thru Help and used Google and have not really found an answer.
I know that Leap Years need to be accounted for too.
Any suggestions on where to start ?
james
 
James,
Use DateTime.AddDays to find a date given the number of days since a "well
known" date. Or to go the other direction use DateTime.Subtract to find the
number of days since a "well known" date.

The "problem" you are going to have is that Datetime.MinDate is 01/01/0001
as opposed to 00/00/0000, I would simply add or subtract the difference in
days as needed...

You may want to consider a pair of functions, something like:

Private Const BaseDate As DateTime = #1/1/1900#
Private Const BaseDays As Integer = 36525

Public Function ToFileDateTime(ByVal value As DateTime) As Integer
Dim ts As TimeSpan = value.Date.Subtract(BaseDate)
Return CInt(ts.TotalDays) + BaseDays
End Function

Public Function FromFileDateTime(ByVal days As Integer) As DateTime
Return BaseDate.AddDays(days - BaseDays)
End Function

Where BaseDate & BaseDays are your "well known" date.

Of course if you need dates pre 01/01/0001 then you may have a problem...

Hope this helps
Jay
 
Jay, great solution! Both functions work very well. After my brain kicked in and figured out how to use them correctly :-)
I really do appreciate your help. The problem I had encountered was that the Dataflex database system
used 00/00/0000 as the Base Date. And that the database engine stored that number in Hex in the original
(DOS based) file system. I could find and extract the numbers, but, I could not find out exactly how the data was being
converted to a meaningful date. I am learning to have a lot more respect for people that write database converters now!
james
 
Thank you for the link Cor. I got Jay's functions working and they seem to do the trick. But, I will investigate the method you
suggest too.
james
 
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
 
Cor,
Remember James wants from date 00/00/0000 not time 00:00:00.

If James format is Date only (as his post suggests) I don't really see how
Ticks are going to help much, as 0 ticks is date 01/01/0001. To get to
00/00/0000 you might need a negative (or even an imaginary) Tick value,
DateTime will not allow a negative Tick value! Plus how many ticks are there
between 00/00/0000 & 01/01/0001? For that matter how many licks does it take
to get to the center of a tootsie pop?

If James format is really a Date/Time, then Ticks may be of help.


It would seem that 00/00/0000 might be in the same realm as the square root
of negative one!


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,
Reading my other post & your post I suspect the "Real" base date is 1/1/1800
(and not 12/31/1799 as we both found) and the original routine has a Y2K
problem :-)

Which may mean that current dates may be off by 2 days :-(

Just a thought
Jay
 
James,

Forget this one, something told me that there was something wrong and
afterwards everybody can see of course 36525/365 = 100 years so 00-00-01 +
100 years is not 1900

See my solution after your message too Jay's

(I was also triggered by the maximum of the timespan, I never checked it,
now I did, I had the idea that Jay had often told in this newsgroup that it
is limited, but it is enough for the solution from Jay).

Cor
 
Jay,

Afterwards I saw that this was much to much work, I made and sent my last
message before I readed yours. Unlucky not in time to save you the time
making this message, however thanks for the attention.

:-)

Cor
 
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
 
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
 
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
 
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
Click to expand...

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
Click to expand...

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
Click to expand...
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
 
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
 
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 said:
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
Click to expand...
<<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
 
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
 
Back
Top