Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: How to Calculate length of time between dates

    I have a query with 2 fields - Start_date & End_date.

    I need a third field in the query to calculate the length between the two dates. I need it in Years, Months, Days if possible.

    eg start_date = 01/01/04
    end_date = 25/4/07

    I need the amount in years and months and days please

    Tom

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Quote Originally Posted by moss2076
    I have a query with 2 fields - Start_date & End_date.

    I need a third field in the query to calculate the length between the two dates. I need it in Years, Months, Days if possible.

    eg start_date = 01/01/04
    end_date = 25/4/07

    I need the amount in years and months and days please

    Tom
    Using the DateDiff function, you can make three fields in your query for example


    PHP Code:
       y:DateDiff("yyyy",start_Dateend_Date)
      
    m:DateDiff("m",start_Dateend_Date)
      
    d:m:DateDiff("d",start_Dateend_Date
    Then you can concatinate them.

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Can it work so I dont have to enter the start and end dates when the query runs? I already have the dates stored in a table and need the query to work it out for me.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hammbakka, that's not what tom wants

    for example, say startdate=1997-08-27 and enddate=2004-08-30

    your three fields are y=7, m=84, d=2560

    you cannot just "concatenate" these values

    the answer is 7 years, 0 months, 3 days

    you cannot just take the number of days difference and divide by 365.25, either

    the formula will involve year(end_date)-year(start_date), then month(end_date)-month(start_date), then day(end_date)-day(start_date), but it's not that simple either

    if you have 2001-11-15 to 2004-08-30, subtracting the years gives 3, but you can easily see that the difference is less than 3 years

    i see a lot of IIFs involved...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    There must be a way to do it in a query!!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, there is, it just involves a lot of IIFs

    consider the "age" calculation:
    Code:
    select year(enddate) 
         - year(startdate) 
         - iif(month(startdate) > month(enddate), 1,
           iif(month(startdate) < month(enddate), 0,
           iif(day(startdate) > day(startdate), 1, 0)))
              as age
      from yourtable
    this returns the number of complete whole years

    what you have to do after that is a datediff on the months (subtracting month numbers won't suffice), with the same adjustment depending on whether the day is less than or greater, and then another datediff on the days
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what would be the desired answer for:
    start date 28th February, 2003
    end date 29th February, 2004
    ??? 1year 0months 0days
    or
    ??? 1year 0months 1day

    compare with your answer for an equivalent period starting one day later:
    start date 1st March, 2003
    end date 1st March, 2004

    and what would be the desired answer for:
    start date 29th February, 2004
    end date 28th February, 2005
    ??? 1years 0months 0days
    or
    ??? 0years 11months 28days

    i would be really tempted to use days only if at all possible

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    what would be the desired answer for:
    start date 28th February, 2003
    end date 29th February, 2004
    ??? 1year 0months 0days
    or
    ??? 1year 0months 1day

    compare with your answer for an equivalent period starting one day later:
    start date 1st March, 2003
    end date 1st March, 2004

    and what would be the desired answer for:
    start date 29th February, 2004
    end date 28th February, 2005
    ??? 1years 0months 0days
    or
    ??? 0years 11months 28days

    i would be really tempted to use days only if at all possible

    izy
    You are EVIL Izy ... Are your horns blushing with pride????
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    izy, those are really easy to answer

    start date 28th February, 2003
    end date 29th February, 2004
    answer: 1 year 0 months 1 day

    start date 1st March, 2003
    end date 1st March, 2004
    answer: 1 year 0 months 0 days

    start date 29th February, 2004
    end date 28th February, 2005
    answer: 1 year 0 months 0 days
    or ....... 0 years 11 months 28days

    depends on your business rule!!

    speaking of business rules, do a google to see which day people who were born on feb 29 actually celebrate their birthday, i.e. when do they say they are one year older

    that'll tell you which rule you want to use
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yes of course, rudy. the questions are easy to answer within any one context - your algo views life from a person's age point of view. it is a perfectly valid viewpoint and your algo (and similar ones you posted here in the past) are 100% correct, providing the conventionally expected answer to the "how old are you?" question.

    in a different context, a person who is due to be executed "one year" from a given date might well be curious to know how many days are left after 11months 28days have elapsed.

    you say you agree that (in general, but maybe not for the "age" question) my third example has two perfectly defensible answers and, as you comment, the "correct" answer depends on an (arbitrary) internal business rule.

    i only suggest that if there is no pre-established business rule, it would be awfully convenient to create one based on days (or decimalYears = days/365pointSomething or decimalMonths = days/30pointSomething). the result would be no more and no less "wrong", it would be radically easier to calculate, and it would convey at least as much information to the casual observer as the 11months 28days answer.

    "i would be really tempted to use days only if at all possible".

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by moss2076
    I have a query with 2 fields - Start_date & End_date.

    I need a third field in the query to calculate the length between the two dates. I need it in Years, Months, Days if possible.

    eg start_date = 01/01/04
    end_date = 25/4/07

    I need the amount in years and months and days please

    Tom
    I got the following from Raskew who posts at

    http://www.access-programmers.co.uk/forums/index.php?

    The new field in a query

    AgeAtDeath: Agecount6([DOB],[DOD])

    With DOB and DOD being date of birth and date of death. The new field returns the answer as 66 years, 2 months, 11 days etc. The field names don't have to be called DOB or DOD. Hope thius helps...Mike

    Agecount6 is a function as follows:

    Function Agecount6(ByVal pdob As Date, _
    Optional ByVal pEdte As Variant, _
    Optional ByVal pWhat As Variant) As String

    '************************************************* ****
    'Purpose: Display age or difference between
    ' two dates with options to display
    ' in any variation of years, months,
    ' days.
    'Coded by: raskew
    'Inputs: 1) ? Agecount6(#3-Mar-80#) 'defaults
    ' to current date & "ymd" display
    '
    ' 2) ? Agecount6(#3-Mar-80#, "4/25/04")
    ' Uses PEdte in place of date(),
    ' and default "ymd" display

    ' 3) ? Agecount6(#3-Mar-80#, "4/25/04", "d")
    ' Same as 2), but with display as days
    '
    'Output: 1) 24 years, 1 month, 15 days
    ' 2) 24 years, 1 month, 22 days
    ' 3) 8819 days
    '************************************************* ****

    Dim dte2 As Date
    Dim dteMyDate As Date
    Dim intHold As Integer
    Dim n As Integer
    Dim strHold As String
    Dim strHold2 As String
    Dim strTemp As String
    Dim strWhat As String

    strWhat = IIf(IsMissing(pWhat), "ymd", pWhat)

    dteMyDate = pdob
    dte2 = IIf(IsMissing(pEdte), Date, pEdte)
    For n = 1 To Len(strWhat)
    strHold = Mid(strWhat, n, 1)
    Select Case strHold

    Case "y"
    intHold = DateDiff("yyyy", dteMyDate, dte2) + _
    (dte2 < DateSerial(Year(dte2), Month(dteMyDate), Day(dteMyDate)))
    dteMyDate = DateAdd("yyyy", intHold, dteMyDate)
    strHold2 = strHold2 & LTrim(Str(intHold)) & " year" & IIf(intHold <> 1, "s, ", ", ")

    Case "m"
    intHold = DateDiff("m", dteMyDate, dte2) + (Day(dteMyDate) > Day(dte2))
    dteMyDate = DateAdd("m", intHold, dteMyDate)
    strHold2 = strHold2 & LTrim(Str(intHold)) & " " & "month" & IIf(intHold <> 1, "s, ", ", ")

    Case "d"
    intHold = DateDiff("d", dteMyDate, dte2)
    strHold2 = strHold2 & LTrim(Str(intHold)) & " " & "day" & IIf(intHold <> 1, "s", "")

    End Select
    Next n

    Agecount6 = strHold2

    End Function

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    omg a loop

    well, as algorithms go, it certainly has a flexible range of output formats

    i wouldn't use it in a high volume query, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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