Calculate quarter-to-date

You can use this to get to the start of the current quarter:
dateadd(q, datediff(q, 0, getdate()), 0)

select *
from scores
where score_date >= dateadd(q, datediff(q, 0, getdate()), 0)

Select allOpen in new window

That looks great -- but I'm guessing it doesn't work because I'm using Access 2003 (???).  I got an error "undefined function 'getdate' in expression".
Is there another way around that?
Thanks for your quick reply.

use date() instead

And it should be
....
where score_date >= dateadd("q", datediff("q", 0, date()), 0)
 

Actually if you are talking about the start of the current quarter, it should be

where score_date >= dateadd("q", datediff("q", 0, date())-1, 0)+1

Select allOpen in new window

Thanks, Ralmada.  I didn't see it was MS Access before.

We're getting a lot closer. :-)
This one works for the beginning of the current quarter.  So, if the file has July 2009, that's the beginning of the 3rd quarter and it works great.
But I'll be reporting on the quarter-to-date of the close of the prior month.  So that would be April 2009 for last month's quarter to date.  Is that just removing the "-1"?  (I haven't been able to break apart what the code is doing yet).
Thanks for the help - to you both.

Sorry -- that's April 2009 as the beginning of last month's quarter to date.  It's also the beginning of the QTD for May and April.

What my original code was doing was getting the quarter difference of today from base date which gets you first date of quarter.  In MS Access it gets the last day, so Ramalda used the quarter difference - 1 to get last day of quarter previous then add one day.

So you can do -2 to get last day of quarter previous to prior period.

And now since you are not getting quarter to date which would be start of current quarter to present, you have to bind the top end of dates as well.  

where score_date >= dateadd("q", datediff("q", 0, date())-2, 0)+1
and score_date < dateadd("q", datediff("q", 0, date())-1, 0)+1

Select allOpen in new window

@mwvisa1, I love this approach, but for some reason the above is not working in my MSAccess enviroment.
So I would suggest this to get the previous quarter

DateAdd("q",DateDiff("q",0,Date())-2,2)

Select allOpen in new window

Think it is the starting date difference for value 0 in MS Access vs MS SQL.  I tested my post above and it works exactly the same as the one for MS SQL with #1900-01-01# in place of 0.

This is cool -- both worked great!  I'm learning a lot here.  :-)  
The datediff is looking at the base date (either 0 or the date that mwvisa1 used) and today's date, right?  This returns a number representing the quarter (it's a big number when I do it).  Then, we subtract 2, to go to the prior quarter.  Then we add 2  ... ???
I'm a bit lost in the logic but it's a great solution either way.
Many thanks again to you both! I hope I can award full points to more than one.
 

oops, I just substituted
DateAdd("q",DateDiff("q",0,Date())-2,2)
for the previous beginning and the other part to end.  So that works to get between April and June.
The other one
DateAdd("q",DateDiff("q",0,Date())-1,2)
Gives July and that would be 4 months of data not the three between April - June

Sorry - that does work because it's "<" 7/1/2009 and it will give everything in between.
Many thanks again.

Excellent job in working through this issue.  I appreciate all of your effort to get the SQL right.

It will give you 1/7/2009, but if you are using < instead of <=, then it will search for values up to June 30 only

DateAdd("q",DateDiff("q",#1900-01-01#,Date())-1,#1900-01-01#)

In MS SQL 0 == #1900-01-01#.

DateDiff("q", #1900-01-01#, Date()) == gets the number of quarters since #1900-01-01# to today's date.
Using DateAdd, we add this result back to base date but instead of today's date you get the 1st day.  This works different in MS Access because I think date 0 is something like 1989-12-30 which is why starting at day 2 works the same.

It is easier to understand that -1 is one quarter back than -2, so good to understand what is happening.

This would also work:

where score_date >= DateAdd("q",DateDiff("q",2,Date())-1,2) 
and score_date < DateAdd("q",DateDiff("q",2,Date()),2) 

Select allOpen in new window

Yes, that's right! 0 is 30-12-1899 in Access. Did a quick test like this
select  format(0, "dd/mm/yyyy")
 

That is interesting.  The datediff is counting all of the quarters from the base date. I wonder if that's going to work each month now because we don't always need to go back one quarter, right?  Sometimes we're in the middle of the quarter.

LOL, yes 1899 not 1989. :)

So, at the end of July (first week of August), want to know QTD for July.  That will be just the one month of July.  But it looks like this will take us back one quarter (sorry if I'm confusing this more).

Yes you will always be reporting a quarter behind the way you asked for it to go back to april.  If you want the current quarter just go with my original suggestion and just do this with our updated knowledge on use of 2 instead of 0.

where score_date >= dateadd("q", datediff("q", 2, date()), 2)

Select allOpen in new window

If you meant to get quarter to date from the last full month, then do it this way:

where score_date >= dateadd("q", datediff("q", 2, dateadd("m", -1, date())), 2)
and score_date < dateadd("q", datediff("q", 2, dateadd("m", -1, date()))+1, 2)

Select allOpen in new window

This month, that will use 2009-04-01 to 2009-07-01.  In August, it will use 2009-07-01 to 2009-10-01, and so on.

I appreciate your patience -- I notice that I didn't ask the original question clearly.
In August, I will look for the quarter-to-date for July.
July will be one month, QTD.
In September, I look to August QTD
August will be 2 months for QTD (July & August).
In October, I look to September.
September will be 3 months for QTD (July, August, Sept).
November I look to October.
October will be back to one month - like July.  

Quarter-to-date is like year-to-date but just starting at the beginning of the quarter through the target month.
So, it's either one, two or three months of data in the calculation.
I think we may need to figure out the month value and then tie it to the quarter.

In August, it will use 2009-07-01 to 2009-10-01, and so on.

Ok, that can't work because we won't have any data for Sept and Oct  in the August report.

This will work for that -- http:#24827346.  You can plug in dates in future instead of date() in the solution to test out.

I think we're very close but not quite.
Maybe we change the code to figure out the beginning of the quarter of the prior month.
So, reporting in September -- we look at the prior month (August).  That is in the 3rd quarter and we measure between the beginning of the 3rd quarter and August.

Yes -- that's right.  It gets the month and then the quarter.  Thanks again.  It does work.

That works great.