UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Exact Year To Find Difference In Date, Access 2016    
 
   
oemar00
post Mar 1 2019, 10:30 AM
Post#1



Posts: 60
Joined: 26-June 09
From: Herndon NOVA


I am using SQL trying to find ID whose duration exceeded 10 years. I do not know how to utilize YEAR in my criteria, so I utilize number of days as follows:
SELECT State, State_ID, Issue_Date, Expiration_Date, DATEDIFF('d',Issue_Date, Expiration_Date) AS DateDiff_in_days
FROM State_Table WHERE DateDiff('d', Issue_Date, Expiration_Date) > 3651
ORDER BY 5 DESC,1,2;

But because of that, the result is inconsistent as some within 10 years is considered as has been expired, as shown in the example below; both example has 3652 days difference:
1. Between 9/10/2009 to 9/10/2019 which is = 10 years + 1 day, is considered as invalid, so this is OK
2. Between 7/6/2010 to 7/5/2020 which is exactly 10 years, this is valid but is considered as invalid

appreciate anyone who can pitch in a good idea.
Thank you.

--------------------
oemar00
UtterAccess enthusiast
Go to the top of the page
 
GroverParkGeorge
post Mar 1 2019, 10:36 AM
Post#2


UA Admin
Posts: 36,201
Joined: 20-June 02
From: Newcastle, WA


Math on days is, as you see, problematic.

Instead, you should rely on the Date Functions in Access, which should do the trick.

I see that you want to identify the "10 year duration" from the same day of the same month each year. Is that correct?

If so, your WHERE clause could be:

WHERE DateAdd("yyyy", 10, Issue_Date) < Expiration_Date


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
oemar00
post Mar 1 2019, 10:43 AM
Post#3



Posts: 60
Joined: 26-June 09
From: Herndon NOVA


Charming!, I get the answer right away, thanks a lot.

Now, how do I make the date difference in in the unit of Years, Months, and Day so instead of saying the difference is 3,652 days I would say 10 Years, 0 Months, and 1 Day.

I was out of Access for 9 years, it seems I need refreshing.
This post has been edited by oemar00: Mar 1 2019, 10:52 AM

--------------------
oemar00
UtterAccess enthusiast
Go to the top of the page
 
GroverParkGeorge
post Mar 1 2019, 11:00 AM
Post#4


UA Admin
Posts: 36,201
Joined: 20-June 02
From: Newcastle, WA


in our archives, there should be at least one sample for that.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
oemar00
post Mar 1 2019, 12:07 PM
Post#5



Posts: 60
Joined: 26-June 09
From: Herndon NOVA


Thank you,
Yes, I found the sample:
'--- CODE START ---
Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,[font="Franklin Gothic Medium"][/font]
ByRef vMonths As Integer, ByRef vDays As Integer)
' Comments : calculates the age in Years, Months and Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
vMonths = DateDiff("m", vDate1, vdate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less than one year
End Sub
'--- CODE END ---

May be what I should ask is, is it possible by using only SQL without VB.

Thank you.

--------------------
oemar00
UtterAccess enthusiast
Go to the top of the page
 
GroverParkGeorge
post Mar 1 2019, 12:17 PM
Post#6


UA Admin
Posts: 36,201
Joined: 20-June 02
From: Newcastle, WA


I doubt it can be done only in SQL but it might be interesting to try.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kfield7
post Mar 1 2019, 02:39 PM
Post#7



Posts: 1,025
Joined: 12-November 03
From: Iowa Lot


See if these aliases don't accomplish the same thing in SQL.


zMonths: DateDiff("m", [vDate1], [vdate2])
zDays: DateDiff("d", DateAdd("m", [zMonths], [vDate1]), [vdate2])
xMonths: Iif([zDays]<0,[zMonths]-1,[zMonths])
vDays: Iif([zDays]<0,DateDiff("d", DateAdd("m", [xMonths], [vDate1]), [vdate2],[zDays])
vYears: int([xMonths] \ 12)
vMonths: [xMonths]-12*[vYears]
This post has been edited by kfield7: Mar 1 2019, 02:39 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 02:58 AM