473,708 Members | 2,436 Online

# Date from Number of Days

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
Nov 21 '05 #1
29 9114
James,
Use DateTime.AddDay s to find a date given the number of days since a "well
known" date. Or to go the other direction use DateTime.Subtra ct to find the
number of days since a "well known" date.

The "problem" you are going to have is that Datetime.MinDat e 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.Subt ract(BaseDate)
Return CInt(ts.TotalDa ys) + BaseDays
End Function

Public Function FromFileDateTim e(ByVal days As Integer) As DateTime
Return BaseDate.AddDay s(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" <jjames700ReMoV eMe at earthlink dot net> wrote in message
news:ed******** ******@TK2MSFTN GP11.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
I know that Leap Years need to be accounted for too.
Any suggestions on where to start ?
james

Nov 21 '05 #2
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
Nov 21 '05 #3
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******** ******@TK2MSFTN GP14.phx.gbl...
James,
Use DateTime.AddDay s to find a date given the number of days since a "well known" date. Or to go the other direction use
DateTime.Subtra ct to find the number of days since a "well known" date.

The "problem" you are going to have is that Datetime.MinDat e 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.Subt ract(BaseDate)
Return CInt(ts.TotalDa ys) + BaseDays
End Function

Public Function FromFileDateTim e(ByVal days As Integer) As DateTime
Return BaseDate.AddDay s(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" <jjames700ReMoV eMe at earthlink dot net> wrote in message news:ed******** ******@TK2MSFTN GP11.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

Nov 21 '05 #4
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******** ******@TK2MSFTN GP11.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

Nov 21 '05 #5
James,

I real doubt it, althouhg not in Jays solution however.

Dim dd As New DateTime(1799, 12, 31)
dd = dd.AddDays(3652 5)
MessageBox.Show (dd.ToString)
= 01 januari 1900

:-)

Cor
Nov 21 '05 #6
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.Subt ract(BaseDate)
Return CInt(ts.TotalDa ys)
End Function

Public Function FromFileDateTim e(ByVal days As Integer) As DateTime
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******** ******@TK2MSFTN GP11.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

Nov 21 '05 #7
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******** ********@TK2MSF TNGP10.phx.gbl. ..
James,

I real doubt it, althouhg not in Jays solution however.

Dim dd As New DateTime(1799, 12, 31)
dd = dd.AddDays(3652 5)
MessageBox.Show (dd.ToString)
= 01 januari 1900

:-)

Cor

Nov 21 '05 #8
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
Nov 21 '05 #9
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
Nov 21 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

### Similar topics

 2 5215 by: androtech | last post by: Hello, I'm looking for a function that returns a date range for a specified week number of the year. I'm not able to find functions like this anywhere. Any pointers/help would be much appreciated. TIA 38 7154 by: | last post by: I have a script... -----