Date Formula

[chomzky]

Hello Folks,

I am trying to create a daily report that will capture inforamtion from a SQL database. The only problem I can;t seem to resolve is a formula to extract ony informations based on yesterdays date, sord of. The report will be ran once per day. It will be ran through Crytal Reports viewer by inexperienced users.

Our business hours are from 9:00 am - 4:00 am the following day. I am trying to work through a formula that will filter out everything inside of this timeline

if {command.Occured} is between Date(-1) and DateTime 9:00am and {command.Occured}= then
{Command.Number}
else
{@frmIsNull}


***{@frmIsNull} is inserted for times there is no inforamtion. Its a corfula that is blank.

 

[HowardHammerman]

Why not simply have the following in your Crystal Reports query?

{command.occured} = currentdate - 1

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[chomzky]

its that easy I will give it a shot...

 

[pmax9999]

If you want to return all records for the previous date regardless of time, Howards solution is the way to go.

It is not clear to me the impact of the business hours, ie can records fall outside of 'standard' business hours? If, for example, you are looking for records from 9:00 am yesterday to before 9:00 am today, you could do this:

[Date]
{command.Occured} >= DateTime(CurrentDate - 1, Time(9,0,0)) and
{command.Occured} < DateTime(CurrentDate, Time(9,0,0))
[/Date]

You could vary the time components in need to ensure you get what

Cheers
Pete