Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006

    Unanswered: query time duration between dates

    I am trying to run a query to discover how long a client has been active with our service. Each client has a start date and end date while he/she is engaged. what i am trying to discover is how many clients remained active for more than 12 weeks, the dates are also in the following format YYMMDD e.g 20080317,
    any help will be much appreciated,
    many thanks

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    coerce the string to a date (you may get away with cdate, or possibly have to chop and reformat the string so that the app sees it as a date)

    having done that use the datediff function to find how many weeks (or days if you prefer, and use that in a where clause to limit results to the correct values.

    so thats going to be something like
    select <my column list> from <mytable> where 
    cdate(mid$(<startdate>,6,2) & "/" & mid$(<startdate>,4,2) & "/" & left$(<startdate>,2),
    cdate(mid$(<enddate>,6,2) & "/" & mid$(<enddate>,4,2) & "/" & left$(<enddate>,2)) >=12
    that will almost certainly not work due to typos, difference in your dates and other shennaigans.. but it should give you an idea / starting point

    part of the problem is storing dates in a string format, You'd be amazed at how often this crops up, and it always causes problems

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    Providing the date fields are defined as a Date data type, you could have a query SQL statement like the following:

    SELECT tblCustomers.RecID, tblCustomers.CustomerID, tblCustomers.CompanyName, tblCustomers.StartDate, tblCustomers.EndDate, Int(([EndDate]-[StartDate])/7) AS WeeksActive
    FROM tblCustomers
    WHERE (((Int(([EndDate]-[StartDate])/7))>12))

    Note - you may want to remove the Int to show weeks like 17.3332

    If they are not Date field types, see healdem's post above to convert them to a date format which you might embed into the above sql statement

    (or I'd highly suggest making them Date field types - you can always change the looks on how it's displayed to YYMMDD in the table design (format = YYMMDD), or on the form, or on the report!! See attached example for this and WeeksActive query)
    Attached Files Attached Files
    Last edited by pkstormy; 03-17-08 at 09:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Apr 2006
    will give it a try thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts