Duration in year and month of dates difference

[NT100]

Hi,

I want to build a query in ACCESS to calculate a duration between today and invoice_date.

I built it as follows

SELECT DATEDIFF(MONTH, Date(), Invoice_Dt) AS [Month DatePart], DATEDIFF(YEAR, Date(), Invoice_Dt) AS [Year DatePart]
FROM tblEquipment

However, it prompted Month and then Year. It seems that the query doesn't work.

Any suggestions on this.

Thank you in advance.

 

[Minty]

DateDiff("m" , ... for months
DateDiff("yyyy" , ... for the year

The syntax you have would work in SQL Server.

Interval	Explanation
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

 

[NT100]

DateDiff("m" , ... for months
DateDiff("yyyy" , ... for the year

The syntax you have would work in SQL Server.

Interval	Explanation
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

That's great!
For example, invoice_Dt is 22/2/2016 and today is 05/05/2018. The duration is 2 years 2 months. How can I calculate with sql?

Thanks in advance.

 

[June7]

Construct field in query with expression using the DateDiff() function https://support.office.com/en-us/article/DateDiff-Function-E6DD7EE6-3D01-4531-905C-E24FC238F85F. However, calculating elapsed time is not really straightforward. How precise do you want? Review http://allenbrowne.com/casu-13.html. Adapt for whatever level of time you want - years, months, days, etc. Another http://www.fontstuff.com/access/acctut05.htm. Rats! Just noticed that tutorial uses international dates in examples. The author is in England. However, the calculation syntax is appropriate.

If you want output to be a string like "2 years 2 months" instead of 2.16666, that will require more complicated code.

And you are showing an international date in your example http://allenbrowne.com/ser-36.html

The dates in your example will return 27 months which = 2.25 years.

DateDiff("m", #2/22/2016#, #05/05/2018#) / 12 = 2.25

 

[NT100]

Construct field in query with expression using the DateDiff() function https://support.office.com/en-us/article/DateDiff-Function-E6DD7EE6-3D01-4531-905C-E24FC238F85F. However, calculating elapsed time is not really straightforward. How precise do you want? Review http://allenbrowne.com/casu-13.html. Adapt for whatever level of time you want - years, months, days, etc. Another http://www.fontstuff.com/access/acctut05.htm. Rats! Just noticed that tutorial uses international dates in examples. The author is in England. However, the calculation syntax is appropriate.

If you want output to be a string like "2 years 2 months" instead of 2.16666, that will require more complicated code.

And you are showing an international date in your example http://allenbrowne.com/ser-36.html

The dates in your example will return 27 months which = 2.25 years.

DateDiff("m", #2/22/2016#, #05/05/2018#) / 12 = 2.25

Thank you for the information. It worked perfectly.

Best.