Impromptu Report-Date conversion run 45 days back?

[BingBong]

Greetings all,
This is my first post.
I am trying to run a report that looks at the last 45 days from today. The dates are set up as YYYYMMDD. The report will run in scheduler everyday automatically and I need to know how to write the query/filter to look at the last 45 days from the NOW().
Any suggestions or help with this would be greatly appreciated.

BingBong
sbingman@empirecat.com

 

[griffindm]

Try this:

Timestamp between now( ) - 045 00:00:00:000 and now() ...

Where you replace Timestamp with your date field. Select now() from the function list. decrement it by selecting Value then Interval, and changing the default to the above value. the first 3 characters are for days.

HTH

Dave Griffin

 

[BingBong]

Dave,
Thank you for your response.
I have tried your suggestion but when I use my date field ("Opndt8" [YYYYMMDD]), the "Now" option is not available under the Functions. Is this because the date needs to be in another format?
So, when the Scheduler activates the report, it will run the report for anything within the 45 day period (starting from today and going back 45 dyas)that I'm looking for?
Should this become a column in the report? Do I build this in the Filter?

 

[griffindm]

I'd suggest including the 'date from' date in the report, if for no other reason than to include in in the title description (so the report user knows the timespan covered in the report). If you do include it this way, you can also refer to it in the filter between clause.

The now() function runs on the database, and different databases return it as either a date or a datetime (which has the time included with the date). In either case, now() and your date column are not in the same format. Use the date-to-datetime or the datetime-to-date functions to get them into the same format (I'd suggest using the date-to-datetime on your data column). then you should be able to do the comparison.

HTH,

Dave Griffin

 

[BingBong]

Can you give me a little more detail, and please explain it to me like I'm a six year old?

 

[griffindm]

First create a new calculated column based on your date field, "Opndt8" (call it Opndt8new) as follows:

Go to the menu option Report | Query | Data.
Create the new column by using the calculate icon on the center of the bar symbolized by a little calculator.
Name the new field Opndt8new.
Click in the Expression box of the dialog, and then select Functions from the Available Components.
Select Date-to-Datetime from the Available Components.
Replace the highlighted area named <date-exp> by selecting the new column you defined above by clicking in the Report Columns folder shown under Available Components.
Now use the new column you just created in the filter. The NOW() option should be available for comparison.

Let me know if you still have problems.

HTH,

Dave Griffin

 

[ChillAPAC]

I've used this in my filter to retrieve a certain number of past days data:
[Date Field] >= add-days(now(),-4)

Where [Date Field] is your database date field. You can insert any value, mine was just going back 4 days.

 

[BingBong]

ChillAPAC,

What format is your date setup in? Mine is setup in YYYYMMDD format. Any help here would be greatly appreciated.

 

[ChillAPAC]

Most my tables have date as dd/mm/yyyy or dd/mm/yyyy HH:MM:SS