Re: ADO adDate Value Calculation


"Philip Siaw" <nospam@xxxxxxxxxxxx> wrote in message
news:euthfvzHHHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
adDate(7) indicates a date value.
A date is stored as a double, the whole part of which is the number of
days since December 30, 1899,
and the fractional part of which is the fraction of a day.

Given a timestamp value for example 2000-12-28 09:30:00
I need to calculate the corresponding adDate Value.

I am able to calculate the number of days since December 30, 1899,
but I don't know the formula for calculating the fraction of a day.

By now practically everything that might even loosely qualify as a computer
language, runtime environment, script engine, etc., has built-in date <->
string conversion functionality built into it. So unless you are looking to
reinvent the wheel...

If you're working with VB6, VBScript (read: anything VB that does not end in
".Net") just coerse the string to a date type, and assign that to the ADO
field. e.g.,

rs.Fields("MyDate").value = CDate("2000-12-28 09:30:00")

To directly answer your question, you'd first need to reduce the time
component of the string to the number of seconds it represents:

Const SecondsPerMin = 60
Const SecondsPerHr = 3600 ' 60 * 60
Const SecondsPerDay = 86400 ' 60 * 60 * 24

Secs = (9 * SecondsPerHr) + (30 * SecondsPerMin) + 0

The decimal fraction can then be calculated like so:

DecFractionOfDay = Secs / SecondsPerDay

Any idea on calculating the fraction of a day, and what is the precision
of the scale?

The effective precision depends on the database engine under which you'll be
storing the date. If that engine stores it natively as a floating point
type, it will use the same scale/precision for all dates, and that of cource
will be the determining factor.

If you happen to be using SQL Server as the db engine (which does not
natively store datetime types as floating point values) the resolution of
datetime values is 1/300th of a second.


-Mark


Thank you.




.

Relevant Pages

  • Re: evaluating simple mathematical expressions
    ... >> What is the simplest way of converting a fractional string like ... > Function FracToDec(ByVal Fraction As String) As Double ... > Dim Blank As Integer ... > Dim Slash As Integer ...
    (microsoft.public.vb.general.discussion)
  • RE: Converting a fraction to decimal
    ... Public Function FractionToDecimal(txtFraction As String) As Single ... 'Element 1 is left side of the fraction ... You can change the formatting to suit yourself, but the leading # in format ... I would like the user to enter inches and then ...
    (microsoft.public.access.forms)
  • Re: evaluating simple mathematical expressions
    ... > What is the simplest way of converting a fractional string like ... Function FracToDec(ByVal Fraction As String) As Double ... Dim Blank As Integer ... Dim Slash As Integer ...
    (microsoft.public.vb.general.discussion)
  • Re: accept fractions from textbox
    ... Function FracToDec(ByVal Fraction As String) As Double ... Dim myValue As Double ... I am having trouble getting this to work in Excel 2007; ...
    (microsoft.public.excel.programming)
  • Re: How to add Fractions along with their formatting as AutoText in a template
    ... Yes I have some fraction macros and some of the images. ... Dim oNum As String ... Dim bInvalid As Boolean ...
    (microsoft.public.word.customization.menustoolbars)