Restrict time range from datetime selection 1

[mipsfuct]

Hello,

I have what appears a simple problem but I cannot seem to resolve it on my own.

I have to generate productivity reports that limit the evaluation time from (Mon 7:00 AM) to (Sun 7:00 AM).

I know how to get Mon - Sun :

({Deal_Jacket.Billing_Comp_Pend} in (currentdate - dayofweek(currentdate)-5) to (currentdate - dayofweek(currentdate))

But I do not know how to restrict it by the time (such as 7:00 AM on Mon).

The fields I am working with are datetime (ie..."1/4/2005 1:55:13 PM") or "MM/DD/YYYY HH:MM:SS AM/PM"

I would greatly appreciate any assistance with this problem, thanks in advance!

 

[rosemaryl]

Mipsfuct,

Change your formula to:

({Deal_Jacket.Billing_Comp_Pend}
in (currentdate - dayofweek(currentdate)-5)+time(07,00,00)
to (currentdate - dayofweek(currentdate)+ time(07,00,00))
)

It generates the following where clause in the Database Show SQL Query:

Deal_Jacket."Billing_Comp_Pend" >= {ts '2005-01-03 07:00:00.00'} AND
Incident."Open Date & Time" < {ts '2005-01-08 07:00:01.00'}

Just a note, that last date is Saturday and not Sunday!

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[mipsfuct]

Thanks Rosemaryl!

Appreciate the advice on posting as well....

I basically finished with this for (Mon-Sun 7:00am-7:00am) if anyone should need this.

({your.datefield} in (currentdate - dayofweek(currentdate)-5) +time(07,00,00) to (currentdate - dayofweek(currentdate)+1)+ time(07,00,00))