Formula for last 30 days of current date? 2

[Nerdhurd]

Howdy!

Id like to add a select statement to my report for the last 30 days to the current date rather then just a static date range. So if I ran it today the range would be april 18 - may 18 and if I ran it tomarrow it would be april 19 - may 19 and so on...Is this possible to do with a formula?

I am using crystal reports 8.5

Thankyou VERY much for any advice!!!

 

[synapsevampire]

That isn't 30 days, that's 1 month.

What do you want if it's Feb 28th? Feb 29th?

Anyway, there are different means...

Report->Edit Selection Formula->Record

{table.date} in currentdate-29 to currentdate

That handles 30 days.

Your example doesn't show 30 days (months don't all have 30 days), so there you might use:

{table.date} in dateserial(year(currentdate),month(currentdate)-1,day(currentdate)) to currentdate

If neither of these are what you need, please think through your requirements and post specifically what you need.

-k

 

[dBjason]

It all depends on what DB server is on your back end.

If it's SQL Server, you'll want to check into these functions (in T-SQL help files):

Month()
GetDate()
DatePart()

You'll need to create a string which will contain something like "BETWEEN ThisDate AND ThatDate" in the WHERE clause. ThatDate will be the current date, ThisDate will be Month(GetDate) - 1. Might be a little confusing but it is possible.

Alternatively, you can create a stored procedure in (almost) any back-end database. It'll need two parameters (start & end date) to query, then return the recordset.

Good luck, hope it was of some help.

Jason

 

[Nerdhurd]

Thank you both for the quick responses!!!

Sorry about the confusion and my poor example, It actually just needed to be the last 30 days. I used

{table.date} in currentdate-29 to currentdate

and it works flawless!

 

[synapsevampire]

Jason: Altering the where clause in a CR 8.5 report is a bad idea and requires that they use Database-Show SQL Query, which you omitted.

the record selection formula is the preferred method for generating where clause criteria, and yes, Crystal passes it to the database if it's done properly.

Nerhurd: don't use that advice, it is occasionally suggested in UNION queries for those unable to create database objects (such as Views or Stored Procedures), but for almost anything else you'll regret it later.

-k