Date and Time Question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Date and Time Question

  1. #1
    Dwight Guest

    Date and Time Question


    I am working with an Access database with dates and times. I have been using
    the DATEDIFF Function in a SQL statement to get the difference between 2
    dates/times. This has been working fine.

    Recently it was asked of me to look at the difference between the 2 dates
    and excluding the times that the business is not in operation. I am not
    having much luck and I am asking for some help if possible.

    Here is an example:

    A work order was created on 02/23/2001 7:30 AM (Friday)
    The mechanic got the work order at 02/26/2001 09:15 AM (Monday)
    The companies work hours are from 6:00 AM - 3:30 PM Monday through Friday
    The answer can be in hours. (11.25 hours)

    And if it wasn't bad enough they asked me to exclude holidays. if I could.
    **** those Monday holidays.


  2. #2
    Ted McNeal Guest

    Re: Date and Time Question


    Dwight,

    I am sure that your initial thought is that this is impossible. It really
    would be insane if you had to implement such functionality just using date
    functions.

    You can do what you want to do by using a table to record the workdays and
    the hours in that workday. This table would be similar to a date dimension
    table in a Star schema database.

    The table should include a record for each day of each year for a time period
    several years off. The table should include the calendar date, a bit field
    for whether the date was a work day, the starting time of work and the ending
    time of work. You will then be able to count the number of work days between
    your order placed date and completed date and compute the number of opportunity
    work hours between.

    You can generate the entries using DAO or ADO by looping from a start date
    to a selected ending date and inserting a record for each date. To further
    expedite your work, you can check if the date is a known day off and flag
    it appropriately.

    This should be enough of an idea to get you started and see how you might
    begin to attack this task.

    Ted McNeal
    tmcneal@mhsinc.org



    "Dwight" <hbliss@frontiernet.net> wrote:
    >
    >I am working with an Access database with dates and times. I have been

    using
    >the DATEDIFF Function in a SQL statement to get the difference between 2
    >dates/times. This has been working fine.
    >
    >Recently it was asked of me to look at the difference between the 2 dates
    >and excluding the times that the business is not in operation. I am not
    >having much luck and I am asking for some help if possible.
    >
    >Here is an example:
    >
    >A work order was created on 02/23/2001 7:30 AM (Friday)
    >The mechanic got the work order at 02/26/2001 09:15 AM (Monday)
    >The companies work hours are from 6:00 AM - 3:30 PM Monday through Friday
    >The answer can be in hours. (11.25 hours)
    >
    >And if it wasn't bad enough they asked me to exclude holidays. if I could.
    > **** those Monday holidays.
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center