Midnight to last full hour 1

[ptrifile]

I have seen some posts on here that have similar outcomes but not exactly what i need and i was unable to modify them properly.

I need to run a report hourly that will show data from midnight till the last full hour. So if i was running the report at 3:05pm it would give me from midnight till 3pm data.

I would like for someone to just run this report without having to change the times....can someone help me?

Thanks in advance...

Paul

 

[lbass]

{table.datetime} in datetime(currentdate,time(0,0,0)) to
dateadd("h",-1, datetime(currentdate,time(hour(currentdatetime),59,59)))

-LB

 

[ptrifile]

lbass, this works great with one exception and im sure its the way the database is setup. The database stores the data in 15 minute intervals, what is happening when i use your query is it is leaving out the last 15 minute interval, so if i run the report right now at 10:12am EST i get data from Midnight till 9:45am and it leaves out the 9:45-10:00am data.

Is there a way i can correct this?

Thanks again for your help!

Paul

 

[ptrifile]

Please disregard my last post.

 

[ptrifile]

Sorry.....I think i do need to modify this. How would i change the above statement to pull everything from midnight to say the previous hour plus 5 minutes? I hope that makes sense.

Paul

 

[lbass]

Please confirm that the 15-min data is broken out like this:

9:30:01 to 9:45:00
9:45:01 to 10:00:00

I don't think the 5 minutes idea is a good approach--if all you are really trying to do is capture the full previous 15-minutes.

-LB

 

[ptrifile]

You are correct that the 15 minute data is broken out exactly like you stated. What would you suggest as a better approach? I really appreciate your help with this!

Paul

 

[lbass]

{table.datetime} in datetime(currentdate,time(0,0,1)) to
datetime(currentdate,time(hour(currentdatetime),0,0)))

-LB

 

[ptrifile]

When i try that i get an error "The remaining text does not appear to be part of the formula"

Could i be doing something wrong?

 

[kray4660]

mis-matched paranthesis. Looks like an extra paran at the end.

 

[lbass]

Yes please remove the last paren--accidental.

-LB