Time and Date formula

[natetc]

Hi,

I have been using simple formulas for my reports and now my boss has asked to add time to this formula.

Before:
The requirement was to pull stats from yesterdays data, formula below:

{W000001.DATETIME} = currentdate-1

Now:
Requirement is to pull stats from 2 days ago at 4pm all the way through yesterdays data. So if today was Wednesday, we require the stats from Monday 16:00 - Tuesday 23:59

This data field {W000001.DATETIME} provides the following info when populated:
8/14/2011 12:30:11PM

Anyone have any ideas how to formulate this? Thanks in advance as this is a little over my head.

 

[MCuthill]

natetc,

Two days, or two business days?
For example: on Monday, would this need to return Thursday at 16:00?

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[natetc]

Hi Mike,

Well I know we have some weekend work, so it would be 7 days a week, but if you could post both formulas that would be a great help to me.

Thanks!

 

[MCuthill]

natetc,

A little embarrassed, I think I have a solution for a 7 day week, but my approach to the other would be far more convoluted than needed.

For a 7-day week, where it is always for two days before prior:

//Provide a lower bound for selection criteria
DateTimeVar TriggerDate:=CurrentDate-2;

DateTime(Year(TriggerDate),Month(TriggerDate),Day(TriggerDate),16,0,0)

This can then be reference in your selection criteria.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

Sorry, posted before complete

A Selection Criteria such as:

{W000001.DATETIME} >= {@YourNewFormula} AND
{W000001.DATETIME} < CurrentDate

Should retrieve the records you seek.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[natetc]

Hi Mike,

So how do I plug this into the formula?

//Provide a lower bound for selection criteriaDateTimeVar TriggerDate:=CurrentDate-2;DateTime(Year(TriggerDate),Month(TriggerDate),Day(TriggerDate),16,0,0)

Selection Criteria works no problem:
{W000001.DATETIME} >= {@YourNewFormula} AND{W000001.DATETIME} < CurrentDate

i'm literally copying and pasting and Crystal doesn't like the first code you provided with the date triggers.

Thanks

 

[MCuthill]

natetc,

You should only need to create a new formula field and copy-paste my formula above (formatted to start new lines same as in my post).

It is then the name of this new formula that is used in the Selection Criteria.

Hope this helps!


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[natetc]

Hi Mike,

When I type this in, here is the error I receive 'a date-time is required here'?

{W000001.DATETIME} >= {@Test} AND{W000001.DATETIME} < CurrentDate

I inputed the formula no problem into the formula fields, but am unsure how to use this formula in the select expert.

Sorry for begin such a beginner at this, I'm learning as we go.

Thanks!

 

[MCuthill]

natetc,

I do not receive this error in my testing file - does it highlight one line or the other (or a portion of one line) when it returns the error?

In the field explorer, right-click your datasource and check the "Show Field Type" box - what data type is {W000001.DATETIME}? (I would assume a datetime, based on name)

Just to be safe, can you also please paste the contents of your formula field {@test}, just in case there is something awry.

Thanks!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[natetc]

I do not receive this error in my testing file - does it highlight one line or the other (or a portion of one line) when it returns the error?

- Not sure

In the field explorer, right-click your datasource and check the "Show Field Type" box - what data type is {W000001.DATETIME}? (I would assume a datetime, based on name)

- Yes, DateTime

Just to be safe, can you also please paste the contents of your formula field {@test}, just in case there is something awry.

- Provided print screens, hopefully this helps
- Changed the name so it was easier to read, but here is the real table title, and no this had no affect on the formula as I corrected it.

W000001.EVENT_DATTIM = WA5U999S.EVENT_DATTIM

 

[MCuthill]

natetc,

You have the whole formula commented out.

As per my previous post, please post the formula as it appears in my 15 Aug 11 11:55 posting.

It should appear like this in your formula editor:

[COLOR=green]//Provide a lower bound for selection criteria[/color]
[COLOR=blue]DateTimeVar[/color] TriggerDate:=[COLOR=blue]CurrentDate[/color]-2;
[COLOR=blue]DateTime[/color]([COLOR=blue]Year[/color](TriggerDate),[COLOR=blue]Month[/color](TriggerDate),[COLOR=blue]Day[/color](TriggerDate),16,0,0)

The selection criteria is returning an error because your current formula returns nothing.

Hope this helps.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

natetc,

In addition to the above, the Selection Expert works okay for record selection - but I would suggest clicking on the "Selection Formulas" folder in the Formula Editor and typing the above selection criteria in there.

I haven't used the Selection Expert to know for certain, but I would suggest it is trying to use your datetime field as a criteria on the STATCD field.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[poujor]

Try using this in your Record Selection Formula

({W000001.DATETIME} >= [blue]DateAdd[/Blue]('h',-32,[blue]cdatetime[/blue]([Blue]CurrentDate[/blue]))[blue])and[/blue]  ({W000001.DATETIME} < [blue]Currentdate[/blue])

I assume your datetime field has the name {W000001.DateTime}

 

[natetc]

Mike and poujor thank you for your help.

At this point I have some understanding of what I am doing and will post back after taking time to figure out exactly how to formulate things. I tend to use the Record Selection Formula and that is why I have more problems.