# Thread: How to Calculate length of time between dates

1. Registered User
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. Registered User
Join Date
Oct 2003
Location
Ger
Posts
1,969
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_Date, end_Date)   m:DateDiff("m",start_Date, end_Date)   d:m:DateDiff("d",start_Date, end_Date)  ```
Then you can concatinate them.

3. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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...

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

6. SQL Consultant
Join Date
Apr 2002
Location
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

7. Cavalier King Charles
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

8. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
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????

9. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740

izy

10. SQL Consultant
Join Date
Apr 2002
Location
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

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

11. Cavalier King Charles
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

12. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
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.
'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)))
strHold2 = strHold2 & LTrim(Str(intHold)) & " year" & IIf(intHold <> 1, "s, ", ", ")

Case "m"
intHold = DateDiff("m", dteMyDate, dte2) + (Day(dteMyDate) > Day(dte2))
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. SQL Consultant
Join Date
Apr 2002
Location
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

#### Posting Permissions

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