Last Full Hour 1

[eric333]

Good morning,

I am attempting to retrieve records based upon a date/time field for the last full hour. Whether I run the report 1 minute or 59 minutes in the current hour, I want all records for the last full hour.

Also, I am attempting to retrieve records based upon the same date/time field for the last 24 hours, but not an actual 24 hours period from midnight to midnight. I need records from 0600 the prior day through 0600 the current day.

Thanks in advance for any assistance anyone can provide.

 

[lbass]

For last full hour, I think you could use:

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

For last 24 hours, you could use:

{table.datetime} in datetime(currentdate-1,time(6,0,0)) to datetime(currentdate,time(5,59,59))

-LB

 

[eric333]

Both worked perfectly. You're absolutely amazing.

For the Last Full Hour, what would I do if I wanted to change it to the last full 15 minutes? In other words, if I ran the report at 1601 or 1614, I would receive records from 1545 through 1559. But, if I ran the report at 1617, I would receive records from 1600 trhough 1614.

 

[lbass]

{table.date} in dateadd("n",-15,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/15)*15),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/15)*15),0)))

-LB

 

[eric333]

Thank you VERY much.

 

[Georgedooley]

hi, I've tried using this formula. I need a formula to use on a report scheduled in crystal server. I want the report to run 5 minutes into each hour showing stats for the previous hour. This report will run between 20:00 and 03:00. The formual gives an error when it goes past midnight. Any ideas?

 

[lbass]

In the formula, did you try replacing:

currentdate

...with:

date({table.datetime})

?

-LB

 

[Georgedooley]

{@datetime} in datetime(currentdate,time(hour(currentdatetime)-1,0,0)) to datetime(currentdate,time(hour(currentdatetime)-1,59,59))

This is the formula i'm using.

i get an error that the time must be between 0 and 23

 

[lbass]

You need to replace currentdate/currentdatetime with your actual fields:

date({table.datetime}) //for currentdate

{table.datetime} //for currentdatetime

-LB

 

[Georgedooley]

thanks. I will try this.

 

[Georgedooley]

i've tried this and i still get the error that the
---------------------------
Crystal Reports
---------------------------
Hour must be between 0 and 23.
---------------------------
OK
---------------------------

I've tried different combinations but cannot get it to work when it runs after midnight looking back at the last hour of the previous day. 23:00 -> 23:59.

Any help would be appreciated.

 

[lbass]

I didn't realize you had changed my suggestion when I responded last. Try:

{@datetime} in dateadd("h",-1,datetime(date({@datetime}),time(hour({@datetime}),0,0))) to
dateadd("s",-1,datetime(date({@datetime}),time(hour({@datetime}),0,0)))

-LB

 

[Georgedooley]

Thanks lbass, its late, I must be missing something, Its reading records but not finding anything. . Its not giving me the error message anymore just reading records and not finding anything. Any ideas what I'm doing wrong?

 

[lbass]

Sorry, I wasn't thinking clearly. {@datetime} can't be anything other than it is--it can't fall into a range one hour before. Meaning you have to reference currentdate and currentdatetime. Try the following.

{@datetime} in dateadd("h",-1,datetime(currentdate,time(hour(currentdatetime),0,0))) to
dateadd("s",-1,datetime(date(currentdate),time(hour(currentdatetime),0,0)))

This won't address the issue or your wanting to run the report five minutes into the hour (a scheduling issue)--it only will choose the previous hour.

-LB

 

[dq99]

Thanks for {table.datetime} in datetime(currentdate,time(hour(currentdatetime)-1,0,0)) to datetime(currentdate,time(hour(currentdatetime)-1,59,59)) lbass - was exactly what I was looking for too!