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
29  8647 
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
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message
news:ed**************@TK2MSFTNGP11.phx.gbl... 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
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
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:Oa**************@TK2MSFTNGP14.phx.gbl... 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
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message news:ed**************@TK2MSFTNGP11.phx.gbl...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
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
"Cor Ligthert" <no************@planet.nl> wrote in message news:e2**************@TK2MSFTNGP11.phx.gbl... 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 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 Ligthert" <no************@planet.nl> wrote in message
news:e2**************@TK2MSFTNGP11.phx.gbl... 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
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
"Cor Ligthert" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... 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
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
"Cor Ligthert" <no************@planet.nl> wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl... 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]" <Ja************@msn.com> wrote in message news:OJ****************@TK2MSFTNGP09.phx.gbl... 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" <no************@planet.nl> wrote in message news:e2**************@TK2MSFTNGP11.phx.gbl... 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" <no************@planet.nl> wrote in message news:ed**************@TK2MSFTNGP11.phx.gbl... 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:O0*************@TK2MSFTNGP10.phx.gbl... 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" <no************@planet.nl> wrote in message
news:ed**************@TK2MSFTNGP11.phx.gbl... 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]" <Ja************@msn.com> wrote in message news:et**************@tk2msftngp13.phx.gbl... 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:uy**************@TK2MSFTNGP09.phx.gbl... 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]" <Ja************@msn.com> wrote in message
news:et**************@tk2msftngp13.phx.gbl... 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" <no************@planet.nl> wrote in message news:OD*************@tk2msftngp13.phx.gbl... 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]" <Ja************@msn.com> wrote in message news:uF**************@TK2MSFTNGP09.phx.gbl... 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
Jay, here is another post I copied that I think contains a clue to the whole thing!!
"create a file containing these statements:
set_date_attribute sysdate4_State to DFTRUE // sysdate returns 4 digit
year
set_date_attribute date4_State to DFTRUE // 2-digit year entries are
automatically converted to 4-digit
set_date_attribute epoch_value to 80 // Year 0 to 80 add 2000, Year
81 to 99 add 1900
Include it in all of your programs, recompile and your problem should be
gone. "
I just know there is a clue in the above statement!! But, then again..........I could just be grasping at straws!!
james
Jay, FINALLY a response from a Dataflex developer that makes some sense and gives good info on the dates. Here is a copy of a
response to my post of Data Access's Newsgroup:
__________________________________________________ _________
James - I think you'll find that the dates are really just integers (AKA
Julian Dates). Any DF programmer who did Y2K work knows the infamous
693975. That's the value for 01/01/1900. Now - you'll also have to test to
make sure all dates are 4 year or 2 year. If 2 year, add the 693975 to' em,
then they'll be 4 year.
So - you've got a bunch of integers, now what? Since this is going to
Access, you could use VB:
--------------
Dim StartingDate as Date, BuildDate as Date, IntValue as Integer,
DFDateValue as Single
StartingDate=DateValue(1900,01,01) 'I think I have the syntax/command
right here - check it!!
'fill DFDateValue with the value from the DF database
IntValue=DFDateValue - 693975 'this will get it down to integer size for
VB
'maybe do some error checking to see if it's > zero
BuildDate = DateAdd(StartingDate,"d",IntValue) 'I think I have the syntax
right here - check it!!
'set the db date to BuildDate
--------------
Please note that my VB may be rusty - so try this at your own risk!
Don't know if this'll work for you - but maybe it's a starting point.
Garret
__________________________________________________ ___________
The fact that the system is using Julian Dates leads to a better understanding of what is going on.
What do you think Jay ?
james
James,
I'm still not seeing any nice dates come out...
What I'm curious about is given your three bytes, how do you get the
DFDateValue value?
The 693975 appears to be an alternate BaseDate constant.
Looking at your original data: 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
It almost appears that the 2nd byte is the year, but its not consistent: I
wonder if its something odd like 2nd, 1st, 3rd...
Hope this helps
Jay
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message
news:ep**************@TK2MSFTNGP12.phx.gbl... Jay, FINALLY a response from a Dataflex developer that makes some sense
and gives good info on the dates. Here is a copy of a response to my post
of Data Access's Newsgroup:
__________________________________________________ _________
James - I think you'll find that the dates are really just integers (AKA
Julian Dates). Any DF programmer who did Y2K work knows the infamous
693975. That's the value for 01/01/1900. Now - you'll also have to test
to
make sure all dates are 4 year or 2 year. If 2 year, add the 693975 to'
em,
then they'll be 4 year.
So - you've got a bunch of integers, now what? Since this is going to
Access, you could use VB:
--------------
Dim StartingDate as Date, BuildDate as Date, IntValue as Integer,
DFDateValue as Single
StartingDate=DateValue(1900,01,01) 'I think I have the syntax/command
right here - check it!!
'fill DFDateValue with the value from the DF database
IntValue=DFDateValue - 693975 'this will get it down to integer size
for
VB
'maybe do some error checking to see if it's > zero
BuildDate = DateAdd(StartingDate,"d",IntValue) 'I think I have the
syntax
right here - check it!!
'set the db date to BuildDate
--------------
Please note that my VB may be rusty - so try this at your own risk!
Don't know if this'll work for you - but maybe it's a starting point.
Garret
__________________________________________________ ___________
The fact that the system is using Julian Dates leads to a better
understanding of what is going on.
What do you think Jay ?
james
Jay, here is what I have managed to get going so far, but, it still gives wrong dates.
Private Sub btnJulian_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnJulian.Click
Dim StartingDate As Date
Dim BuildDate As Date
Dim IntValue As Integer
Dim DFDateValue As Single
DFDateValue = 832213 'should be 09/09/2004
StartingDate = DateValue("1900, 1, 1")
IntValue = DFDateValue - 693975
BuildDate = DateAdd(DateInterval.Day, IntValue, StartingDate)
TextBox2.Text = BuildDate.ToString
End Sub
Instead of getting : 09/09/2004 I get this with the above code: 6/26/2278 12:00:00 AM
I think this is because the Integer values are for Julian Dates and VB.NET is not interacting correctly with it. Especially ,
since I used the DateInterval.Day.
Well, still, it is more information than I started out with.
james
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:Oi**************@TK2MSFTNGP09.phx.gbl... James,
I'm still not seeing any nice dates come out...
What I'm curious about is given your three bytes, how do you get the DFDateValue value?
The 693975 appears to be an alternate BaseDate constant.
Looking at your original data:
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
It almost appears that the 2nd byte is the year, but its not consistent: I wonder if its something odd like 2nd, 1st, 3rd...
Hope this helps
Jay
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message news:ep**************@TK2MSFTNGP12.phx.gbl... Jay, FINALLY a response from a Dataflex developer that makes some sense and gives good info on the dates. Here is a copy of
a response to my post of Data Access's Newsgroup:
__________________________________________________ _________
James - I think you'll find that the dates are really just integers (AKA
Julian Dates). Any DF programmer who did Y2K work knows the infamous
693975. That's the value for 01/01/1900. Now - you'll also have to test to
make sure all dates are 4 year or 2 year. If 2 year, add the 693975 to' em,
then they'll be 4 year.
So - you've got a bunch of integers, now what? Since this is going to
Access, you could use VB:
--------------
Dim StartingDate as Date, BuildDate as Date, IntValue as Integer,
DFDateValue as Single
StartingDate=DateValue(1900,01,01) 'I think I have the syntax/command
right here - check it!!
'fill DFDateValue with the value from the DF database
IntValue=DFDateValue - 693975 'this will get it down to integer size for
VB
'maybe do some error checking to see if it's > zero
BuildDate = DateAdd(StartingDate,"d",IntValue) 'I think I have the syntax
right here - check it!!
'set the db date to BuildDate
--------------
Please note that my VB may be rusty - so try this at your own risk!
Don't know if this'll work for you - but maybe it's a starting point.
Garret
__________________________________________________ ___________
The fact that the system is using Julian Dates leads to a better understanding of what is going on.
What do you think Jay ?
james
James,
That was my latest result as well however than I looked at the differences
in your dates (I started with the first 3 and after a while I recongized you
wrote it in US dates and it was even more crazy). That should be easy to get
the trick. However I did not see the key too that. It is of course something
simple and than oh.
Cor
James,
I was seeing dates in that range (2278) also. I think this is because the Integer values are for Julian Dates and
VB.NET is not interacting correctly with it. Especially , since I used
the DateInterval.Day.
DFDateValue = 832213 'should be 09/09/2004
Are you certain that 832213 should be 9/9/2004?
I would expect 732212 to be 9/9/2004,
as there are 39237 days between 1/1/1900 & 9/9/2004
39237 + 693975 = 732212.
Interesting 100001 days off...
If you use:
Private Const BaseDate As DateTime = #1/1/1900#
Private Const BaseDays2 As Integer = 693975
Public Function ToFileDateTime(ByVal value As DateTime) As Integer
Dim ts As TimeSpan = value.Date.Subtract(BaseDate)
Return CInt(ts.TotalDays) + BaseDays2
End Function
Public Function FromFileDateTime(ByVal days As Integer) As DateTime
Return BaseDate.AddDays(days - BaseDays2)
End Function
Dim theBytes As String = "82 97 27"
Dim theDays As Integer = CInt(theBytes.Replace(" ", "")) - 100001
Dim theDate = FromFileDateTime(theDays)
You get 11/19/1997 as expected.
In fact given your initial column of data, they all match expect 1/8/1999 =
83 01 73
I would probably move the 100001 adjustment into the two routines...
Hope this helps
Jay
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... Jay, here is what I have managed to get going so far, but, it still gives
wrong dates.
Private Sub btnJulian_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnJulian.Click
Dim StartingDate As Date
Dim BuildDate As Date
Dim IntValue As Integer
Dim DFDateValue As Single
DFDateValue = 832213 'should be 09/09/2004
StartingDate = DateValue("1900, 1, 1")
IntValue = DFDateValue - 693975
BuildDate = DateAdd(DateInterval.Day, IntValue, StartingDate)
TextBox2.Text = BuildDate.ToString
End Sub
Instead of getting : 09/09/2004 I get this with the above code:
6/26/2278 12:00:00 AM
I think this is because the Integer values are for Julian Dates and
VB.NET is not interacting correctly with it. Especially , since I used
the DateInterval.Day.
Well, still, it is more information than I started out with.
james
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:Oi**************@TK2MSFTNGP09.phx.gbl... James,
I'm still not seeing any nice dates come out...
What I'm curious about is given your three bytes, how do you get the
DFDateValue value?
The 693975 appears to be an alternate BaseDate constant.
Looking at your original data:
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
It almost appears that the 2nd byte is the year, but its not consistent:
I wonder if its something odd like 2nd, 1st, 3rd...
Hope this helps
Jay
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message
news:ep**************@TK2MSFTNGP12.phx.gbl... Jay, FINALLY a response from a Dataflex developer that makes some sense
and gives good info on the dates. Here is a copy of a response to my
post of Data Access's Newsgroup:
__________________________________________________ _________
James - I think you'll find that the dates are really just integers (AKA
Julian Dates). Any DF programmer who did Y2K work knows the infamous
693975. That's the value for 01/01/1900. Now - you'll also have to
test to
make sure all dates are 4 year or 2 year. If 2 year, add the 693975 to'
em,
then they'll be 4 year.
So - you've got a bunch of integers, now what? Since this is going to
Access, you could use VB:
--------------
Dim StartingDate as Date, BuildDate as Date, IntValue as Integer,
DFDateValue as Single
StartingDate=DateValue(1900,01,01) 'I think I have the syntax/command
right here - check it!!
'fill DFDateValue with the value from the DF database
IntValue=DFDateValue - 693975 'this will get it down to integer size
for
VB
'maybe do some error checking to see if it's > zero
BuildDate = DateAdd(StartingDate,"d",IntValue) 'I think I have the
syntax
right here - check it!!
'set the db date to BuildDate
--------------
Please note that my VB may be rusty - so try this at your own risk!
Don't know if this'll work for you - but maybe it's a starting point.
Garret
__________________________________________________ ___________
The fact that the system is using Julian Dates leads to a better
understanding of what is going on.
What do you think Jay ?
james
Doha! In fact given your initial column of data, they all match expect 1/8/1999
= 83 01 73
Just noticed a couple were off:
82 97 27: expected=11/19/1997, calculated=11/19/1997
82 98 10: expected=2/10/1998, calculated=2/10/1998
82 98 80: expected=4/21/1998, calculated=4/21/1998
83 01 73: expected=1/8/1999, calculated=2/8/1999 *
83 02 02: expected=3/9/1999, calculated=3/9/1999
83 02 12: expected=3/19/1999, calculated=3/19/1999
82 98 81: expected=4/22/1999, calculated=4/22/1998 *
Hope this helps
Jay
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:O%****************@TK2MSFTNGP09.phx.gbl... James,
I was seeing dates in that range (2278) also.
I think this is because the Integer values are for Julian Dates and
VB.NET is not interacting correctly with it. Especially , since I used
the DateInterval.Day.
DFDateValue = 832213 'should be 09/09/2004
Are you certain that 832213 should be 9/9/2004?
I would expect 732212 to be 9/9/2004,
as there are 39237 days between 1/1/1900 & 9/9/2004
39237 + 693975 = 732212.
Interesting 100001 days off...
If you use:
Private Const BaseDate As DateTime = #1/1/1900#
Private Const BaseDays2 As Integer = 693975
Public Function ToFileDateTime(ByVal value As DateTime) As Integer
Dim ts As TimeSpan = value.Date.Subtract(BaseDate)
Return CInt(ts.TotalDays) + BaseDays2
End Function
Public Function FromFileDateTime(ByVal days As Integer) As DateTime
Return BaseDate.AddDays(days - BaseDays2)
End Function
Dim theBytes As String = "82 97 27"
Dim theDays As Integer = CInt(theBytes.Replace(" ", "")) - 100001
Dim theDate = FromFileDateTime(theDays)
You get 11/19/1997 as expected.
In fact given your initial column of data, they all match expect 1/8/1999
= 83 01 73
I would probably move the 100001 adjustment into the two routines...
Hope this helps
Jay
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... Jay, here is what I have managed to get going so far, but, it still gives
wrong dates.
Private Sub btnJulian_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnJulian.Click
Dim StartingDate As Date
Dim BuildDate As Date
Dim IntValue As Integer
Dim DFDateValue As Single
DFDateValue = 832213 'should be 09/09/2004
StartingDate = DateValue("1900, 1, 1")
IntValue = DFDateValue - 693975
BuildDate = DateAdd(DateInterval.Day, IntValue, StartingDate)
TextBox2.Text = BuildDate.ToString
End Sub
Instead of getting : 09/09/2004 I get this with the above code:
6/26/2278 12:00:00 AM
I think this is because the Integer values are for Julian Dates and
VB.NET is not interacting correctly with it. Especially , since I used
the DateInterval.Day.
Well, still, it is more information than I started out with.
james
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:Oi**************@TK2MSFTNGP09.phx.gbl... James,
I'm still not seeing any nice dates come out...
What I'm curious about is given your three bytes, how do you get the
DFDateValue value?
The 693975 appears to be an alternate BaseDate constant.
Looking at your original data:
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
It almost appears that the 2nd byte is the year, but its not consistent:
I wonder if its something odd like 2nd, 1st, 3rd...
Hope this helps
Jay
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message
news:ep**************@TK2MSFTNGP12.phx.gbl...
Jay, FINALLY a response from a Dataflex developer that makes some sense
and gives good info on the dates. Here is a copy of a response to my
post of Data Access's Newsgroup:
__________________________________________________ _________
James - I think you'll find that the dates are really just integers
(AKA
Julian Dates). Any DF programmer who did Y2K work knows the infamous
693975. That's the value for 01/01/1900. Now - you'll also have to
test to
make sure all dates are 4 year or 2 year. If 2 year, add the 693975
to' em,
then they'll be 4 year.
So - you've got a bunch of integers, now what? Since this is going to
Access, you could use VB:
--------------
Dim StartingDate as Date, BuildDate as Date, IntValue as Integer,
DFDateValue as Single
StartingDate=DateValue(1900,01,01) 'I think I have the
syntax/command
right here - check it!!
'fill DFDateValue with the value from the DF database
IntValue=DFDateValue - 693975 'this will get it down to integer size
for
VB
'maybe do some error checking to see if it's > zero
BuildDate = DateAdd(StartingDate,"d",IntValue) 'I think I have the
syntax
right here - check it!!
'set the db date to BuildDate
--------------
Please note that my VB may be rusty - so try this at your own risk!
Don't know if this'll work for you - but maybe it's a starting point.
Garret
__________________________________________________ ___________
The fact that the system is using Julian Dates leads to a better
understanding of what is going on.
What do you think Jay ?
james
<now , were is that little icon with the blushing face?> Huh? OH! I'm typing a response to Jay!!!
How about :
83 01 73: expected = 02/08/1999
82 98 81: expected= 04/22/1998
I admit it, I made a couple of typing errors in my original post when I listed those dates and Values!!
I should have reread my list before now!
Dang Jay, you are good at this stuff!
james
<ducks head>
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:e1**************@TK2MSFTNGP09.phx.gbl... Doha! In fact given your initial column of data, they all match expect 1/8/1999 = 83 01 73
Just noticed a couple were off:
82 97 27: expected=11/19/1997, calculated=11/19/1997
82 98 10: expected=2/10/1998, calculated=2/10/1998
82 98 80: expected=4/21/1998, calculated=4/21/1998
83 01 73: expected=1/8/1999, calculated=2/8/1999 *
83 02 02: expected=3/9/1999, calculated=3/9/1999
83 02 12: expected=3/19/1999, calculated=3/19/1999
82 98 81: expected=4/22/1999, calculated=4/22/1998 *
Hope this helps
Jay
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:O%****************@TK2MSFTNGP09.phx.gbl... James,
I was seeing dates in that range (2278) also.
I think this is because the Integer values are for Julian Dates and VB.NET is not interacting correctly with it. Especially
, since I used the DateInterval.Day.
DFDateValue = 832213 'should be 09/09/2004
Are you certain that 832213 should be 9/9/2004?
I would expect 732212 to be 9/9/2004,
as there are 39237 days between 1/1/1900 & 9/9/2004
39237 + 693975 = 732212.
Interesting 100001 days off...
If you use:
Private Const BaseDate As DateTime = #1/1/1900#
Private Const BaseDays2 As Integer = 693975
Public Function ToFileDateTime(ByVal value As DateTime) As Integer
Dim ts As TimeSpan = value.Date.Subtract(BaseDate)
Return CInt(ts.TotalDays) + BaseDays2
End Function
Public Function FromFileDateTime(ByVal days As Integer) As DateTime
Return BaseDate.AddDays(days - BaseDays2)
End Function
Dim theBytes As String = "82 97 27"
Dim theDays As Integer = CInt(theBytes.Replace(" ", "")) - 100001
Dim theDate = FromFileDateTime(theDays)
You get 11/19/1997 as expected.
In fact given your initial column of data, they all match expect 1/8/1999 = 83 01 73
I would probably move the 100001 adjustment into the two routines...
Hope this helps
Jay
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message news:%2****************@TK2MSFTNGP09.phx.gbl... Jay, here is what I have managed to get going so far, but, it still gives wrong dates.
Private Sub btnJulian_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnJulian.Click
Dim StartingDate As Date
Dim BuildDate As Date
Dim IntValue As Integer
Dim DFDateValue As Single
DFDateValue = 832213 'should be 09/09/2004
StartingDate = DateValue("1900, 1, 1")
IntValue = DFDateValue - 693975
BuildDate = DateAdd(DateInterval.Day, IntValue, StartingDate)
TextBox2.Text = BuildDate.ToString
End Sub
Instead of getting : 09/09/2004 I get this with the above code: 6/26/2278 12:00:00 AM
I think this is because the Integer values are for Julian Dates and VB.NET is not interacting correctly with it. Especially
, since I used the DateInterval.Day.
Well, still, it is more information than I started out with.
james
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:Oi**************@TK2MSFTNGP09.phx.gbl...
James,
I'm still not seeing any nice dates come out...
What I'm curious about is given your three bytes, how do you get the DFDateValue value?
The 693975 appears to be an alternate BaseDate constant.
Looking at your original data:
> 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
It almost appears that the 2nd byte is the year, but its not consistent: I wonder if its something odd like 2nd, 1st,
3rd...
Hope this helps
Jay
"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message news:ep**************@TK2MSFTNGP12.phx.gbl...
> Jay, FINALLY a response from a Dataflex developer that makes some sense and gives good info on the dates. Here is a copy
> of a response to my post of Data Access's Newsgroup:
>
> __________________________________________________ _________
>
> James - I think you'll find that the dates are really just integers (AKA
> Julian Dates). Any DF programmer who did Y2K work knows the infamous
> 693975. That's the value for 01/01/1900. Now - you'll also have to test to
> make sure all dates are 4 year or 2 year. If 2 year, add the 693975 to' em,
> then they'll be 4 year.
>
> So - you've got a bunch of integers, now what? Since this is going to
> Access, you could use VB:
> --------------
> Dim StartingDate as Date, BuildDate as Date, IntValue as Integer,
> DFDateValue as Single
> StartingDate=DateValue(1900,01,01) 'I think I have the syntax/command
> right here - check it!!
>
> 'fill DFDateValue with the value from the DF database
>
> IntValue=DFDateValue - 693975 'this will get it down to integer size for
> VB
>
> 'maybe do some error checking to see if it's > zero
>
> BuildDate = DateAdd(StartingDate,"d",IntValue) 'I think I have the syntax
> right here - check it!!
>
> 'set the db date to BuildDate
> --------------
>
> Please note that my VB may be rusty - so try this at your own risk!
>
> Don't know if this'll work for you - but maybe it's a starting point.
>
> Garret
> __________________________________________________ ___________
>
> The fact that the system is using Julian Dates leads to a better understanding of what is going on.
> What do you think Jay ?
> james
>
>
>
>
>
>
>
>
>
>
>
Jay, thank you for the code. It really works. I have been testing it with several "correct" values :-) and it works good. I will
work it into my file reader and see how well it handles things on the fly.
james
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:e1**************@TK2MSFTNGP09.phx.gbl... Doha! In fact given your initial column of data, they all match expect 1/8/1999 = 83 01 73
Just noticed a couple were off:
82 97 27: expected=11/19/1997, calculated=11/19/1997
82 98 10: expected=2/10/1998, calculated=2/10/1998
82 98 80: expected=4/21/1998, calculated=4/21/1998
83 01 73: expected=1/8/1999, calculated=2/8/1999 *
83 02 02: expected=3/9/1999, calculated=3/9/1999
83 02 12: expected=3/19/1999, calculated=3/19/1999
82 98 81: expected=4/22/1999, calculated=4/22/1998 *
Hope this helps
Jay
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:O%****************@TK2MSFTNGP09.phx.gbl... James,
I was seeing dates in that range (2278) also.
I think this is because the Integer values are for Julian Dates and VB.NET is not interacting correctly with it. Especially
, since I used the DateInterval.Day.
DFDateValue = 832213 'should be 09/09/2004
Are you certain that 832213 should be 9/9/2004?
I would expect 732212 to be 9/9/2004,
as there are 39237 days between 1/1/1900 & 9/9/2004
39237 + 693975 = 732212.
Interesting 100001 days off...
If you use:
Private Const BaseDate As DateTime = #1/1/1900#
Private Const BaseDays2 As Integer = 693975
Public Function ToFileDateTime(ByVal value As DateTime) As Integer
Dim ts As TimeSpan = value.Date.Subtract(BaseDate)
Return CInt(ts.TotalDays) + BaseDays2
End Function
Public Function FromFileDateTime(ByVal days As Integer) As DateTime
Return BaseDate.AddDays(days - BaseDays2)
End Function
Dim theBytes As String = "82 97 27"
Dim theDays As Integer = CInt(theBytes.Replace(" ", "")) - 100001
Dim theDate = FromFileDateTime(theDays)
You get 11/19/1997 as expected.
In fact given your initial column of data, they all match expect 1/8/1999 = 83 01 73
I would probably move the 100001 adjustment into the two routines...
Hope this helps
Jay This discussion thread is closed Replies have been disabled for this discussion. Similar topics
2 posts
views
Thread by androtech |
last post: by
|
38 posts
views
Thread by |
last post: by
|
5 posts
views
Thread by BlackFireNova |
last post: by
|
5 posts
views
Thread by Kiran |
last post: by
|
9 posts
views
Thread by mistral |
last post: by
|
4 posts
views
Thread by jamesyreid |
last post: by
|
7 posts
views
Thread by No bother |
last post: by
| | | | | | | | | | | | | | | | | | | | | | | |
