Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to get the date values between two date values

Discussion in 'Information Technology' started by GL, Jul 28, 2006.

  1. GL

    GL
    Expand Collapse
    Guest

    Hi,

    I have as field values two dates that correspond to a period. However for my
    program I need to have the dates of the time period as single date values.
    Do you have any idea how it is possible to get the transformation?

    Thank you

    GL
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 16 Jun 2006 22:56:01 -0700, GL <GL@discussions.microsoft.com>
    wrote:

    >Hi,
    >
    >I have as field values two dates that correspond to a period. However for my
    >program I need to have the dates of the time period as single date values.
    >Do you have any idea how it is possible to get the transformation?
    >
    >Thank you
    >
    >GL


    That depends on how you want to do the transformation. If you have the
    period 4/1/2006 through 6/30/2006, what do you want to be "the single
    date value"? What if the range were 4/1 through 12/31?

    This isn't a technical question so much as it is a business rule
    question. The program can probably give you any answer you want - but
    what do you want???

    John W. Vinson[MVP]
     
  4. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Do you mean you want to return every date in the range defined by the two
    dates? If so you can either compute the dates in code by incrementing the
    value by 1 in each iteration of a loop starting with the earlier date and
    ending with the later, or you can create a Calendar table, simply a table of
    all dates over a period (10 or 20 years say). An easy way to do this is to
    fill down a column in Excel serially with dates and import the worksheet into
    Access as a table. You then return the dates in a query such as:

    SELECT YourTable.SomeField, Calendar.CalDate
    FROM YourTable, Calendar
    WHERE Calendar.CalDate
    BETWEEN YourTable.StartDate AND YourTable.EndDate;

    Ken Sheridan
    Stafford, England

    "GL" wrote:

    > Hi,
    >
    > I have as field values two dates that correspond to a period. However for my
    > program I need to have the dates of the time period as single date values.
    > Do you have any idea how it is possible to get the transformation?
    >
    > Thank you
    >
    > GL
    >
     
  5. GL

    GL
    Expand Collapse
    Guest

    I am sorry I was not clear.
    I have a table with several fields, two of the fields are called
    StartEventDate and EndEventDate.
    Let’s say that in a record these two fields have the values 02/04/2006 and
    20/04/2006. What I need is two get all the values between i.e. 03/04/2006,
    04/04/2006,…, 18/04/2006, 19/04/2006.
    The next record may have different StartEventDate and EndEventDate so I have
    to get a different range of values and so on.

    GL

    "Ken Sheridan" wrote:

    > Do you mean you want to return every date in the range defined by the two
    > dates? If so you can either compute the dates in code by incrementing the
    > value by 1 in each iteration of a loop starting with the earlier date and
    > ending with the later, or you can create a Calendar table, simply a table of
    > all dates over a period (10 or 20 years say). An easy way to do this is to
    > fill down a column in Excel serially with dates and import the worksheet into
    > Access as a table. You then return the dates in a query such as:
    >
    > SELECT YourTable.SomeField, Calendar.CalDate
    > FROM YourTable, Calendar
    > WHERE Calendar.CalDate
    > BETWEEN YourTable.StartDate AND YourTable.EndDate;
    >
    > Ken Sheridan
    > Stafford, England
    >
    > "GL" wrote:
    >
    > > Hi,
    > >
    > > I have as field values two dates that correspond to a period. However for my
    > > program I need to have the dates of the time period as single date values.
    > > Do you have any idea how it is possible to get the transformation?
    > >
    > > Thank you
    > >
    > > GL
    > >

    >
     
  6. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    That's nearly exactly what the query I sent you will do. In its result set
    the 'SomeField' column would show the value from that field in each row of
    the table (you can of course also include other fields in the query's SELECT
    clause) for as many days as there are in the range for each row, and the
    CalDate field from the Calendar table would show each of the dates in the
    range for each row. So the result would look like:

    SomeField CalDate
    ABC 03/04/2006
    ABC 04/04/2006
    ..
    ..
    ABC 19/04/2006
    ABC 20/04/2006
    XYZ 05/06/2006
    XYZ 06/06/2006
    ..
    ..
    XYZ 10/07/2006
    XYZ 11/07/2006

    because a BETWEEN....AND operation is inclusive. To exclude the first and
    last days of each range you just need to amend the operation slightly:

    SELECT YourTable.SomeField, Calendar.CalDate
    FROM YourTable, Calendar
    WHERE Calendar.CalDate > YourTable.StartEventDate
    AND Calendar.CalDate < YourTable.EndEventDate;

    Ken Sheridan
    Stafford, England

    "GL" wrote:

    > I am sorry I was not clear.
    > I have a table with several fields, two of the fields are called
    > StartEventDate and EndEventDate.
    > Let’s say that in a record these two fields have the values 02/04/2006 and
    > 20/04/2006. What I need is two get all the values between i.e. 03/04/2006,
    > 04/04/2006,…, 18/04/2006, 19/04/2006.
    > The next record may have different StartEventDate and EndEventDate so I have
    > to get a different range of values and so on.
    >
    > GL
    >
    > "Ken Sheridan" wrote:
    >
    > > Do you mean you want to return every date in the range defined by the two
    > > dates? If so you can either compute the dates in code by incrementing the
    > > value by 1 in each iteration of a loop starting with the earlier date and
    > > ending with the later, or you can create a Calendar table, simply a table of
    > > all dates over a period (10 or 20 years say). An easy way to do this is to
    > > fill down a column in Excel serially with dates and import the worksheet into
    > > Access as a table. You then return the dates in a query such as:
    > >
    > > SELECT YourTable.SomeField, Calendar.CalDate
    > > FROM YourTable, Calendar
    > > WHERE Calendar.CalDate
    > > BETWEEN YourTable.StartDate AND YourTable.EndDate;
    > >
    > > Ken Sheridan
    > > Stafford, England
    > >
    > > "GL" wrote:
    > >
    > > > Hi,
    > > >
    > > > I have as field values two dates that correspond to a period. However for my
    > > > program I need to have the dates of the time period as single date values.
    > > > Do you have any idea how it is possible to get the transformation?
    > > >
    > > > Thank you
    > > >
    > > > GL
    > > >

    > >
     

Share This Page