Getting a Quarterly Query to Start at 1200AM and end at 1159PM

[chrisorona]

I am trying to run a Quarterly Query using the date format m/dd/yyyy hh/mm/ss am/pm. I have events that start at the end of the quarter and run into the next quarter and I need these separated. I need the query to start at 12:00:00am on the first day of the query and end at 11:59:59 on the last day of the quarter. I am trying to determine the amount of hours have elapsed per event within that quarter. Below are the Functions I am using.

To determine the Quarter I Want I am using-------------Quarter: DatePart("q",[BeginTime],[EndTime])
to determine the Duration of Event I am Using-----------Duration: ((Round((DateDiff("n",[Begintime],[EndTime])/60),2)))



Thank you,

Chris

[June7]

What is the issue? What is not working? Is there an error message, wrong results, nothing happens?

[chrisorona]

I am getting improper results. An example of this is I have an event that starts on 9/30/2014 at 4:30 PM and goes until 10/8/2014 at 10:40pm I am trying to only get the hours that occurred on 9/30/2014 because the quarter ends that day and the next quarter begins the next day.

[June7]

Yes, records that encompass a range that crosses some demarcation point (daily, weekly, monthly quarterly, etc) are always a frustrating complication. I have tried to help with this sort of issue before and it never gets easier. Requires taking the record range dates and testing if they are beyond the desired range parameters and then deducting any extra hours. Doing this analysis within a query is nearly impossible and probably will require a custom function.