MS Access SQL query for testing dates for last day of the quarter

1
votes

OK, so I have a load of records in a table and they have many different dates. I want to retern only those records whose date falls on the last day of whatever quarter it's in.

I.e. I basically need the equivalent of a lastDayOfQuarter(date) function that calculates the date that is the last day in the quarter for the date passed to it.

e.g. lastDayOfQuarter(#16/05/2013#) = #30/06/2013#

My query might look like:

SELECT * FROM mytable
WHERE mytable.rdate = lastDayOfQuarter(mytable.rdate);

This query will be run over PDO so no VBA allowed. Native MS Access only. I would also prefer to not use string manipulation as there is a difference between US and EU dates which might cause issues down the line.

3
sqlms-access
Maybe this gonna help you : HERE and also look HERE – Kooki
Ahem. You could add WHERE Month(m.rDate) In(3,6,9,12) to the query in the accepted answer to this question: stackoverflow.com/q/17830458/77335 Nice teamwork there, Harry. :-) – HansUp
Yea have done that. Thanks for your help – harryg

3 Answers

1
votes

I'm answering myself as, with the help of HansUp answering a previous question of mine for finding month-end records, I found out quite an easy way to acheive this:

WHERE DateValue(m.rdate) = DateSerial(Year(m.rdate), Month(m.rdate) + 1, 0)
  AND Month(m.rdate) IN(3,6,9,12)
0
votes

the "last day of the quarter" could be different for different users. You may be best to build a table of "lastdays" based on your business rules, then use that table in your query.

0
votes

And here as short answer...Try

Select DateAdd(day, -1, dateadd(qq, DATEDIFF(qq, 0, 'year-month-day'), 0))

For today it should give you

2013-06-30 00:00:00.000

SO for your Table you should use :

SELECT * FROM mytable
WHERE mytable.rdate = DateAdd(day, -1, dateadd(qq, DATEDIFF(qq, 0, mytable.rdate), 0));