Relative Dates

system · December 17, 1998, 8:36pm

I would like to be able to use relative dates as conditions for date fields. For instance, I may want to see all invoices dated or or or , etc… etc… I do not want to have to hard code dates into the conditions every time I want to use them. I can do this using filters, but by that time I’ve already retrieved EVERY invoice from the database and brought it down to the client. I need to be able to do this in either the Query panel, or (better yet) in the Designer module so I can reuse them in many reports w/o having to redefine them.

We’re BO 4.1.2

Thanks!

Listserv Archives (BOB member since 2002-06-25)

system · December 17, 1998, 8:36pm

I would like to be able to use relative dates as conditions for date fields.
For instance, I may want to see all invoices dated or
or or , etc… etc… I do not want to have to hard
code dates into the conditions every time I want to use them. I can do this
using filters, but by that time I’ve already retrieved EVERY invoice from the
database and brought it down to the client. I need to be able to do this in
either the Query panel, or (better yet) in the Designer module so I can reuse
them in many reports w/o having to redefine them.

We’re BO 4.1.2

Thanks!

Listserv Archives (BOB member since 2002-06-25)

system · December 28, 1998, 4:35am

In a message dated 98-12-26 23:52:16 EST, you write:

I would like to be able to use relative dates as conditions for date fields.
For instance, I may want to see all invoices dated or

or or , etc… etc… I do not want to have to
hard
code dates into the conditions every time I want to use them.

Hi, Lisa…

You can do a variety of tricks with objects to accomplish this. If memory serves, you are using the Oracle database. You can use objects that reference the SYSDATE value to accomplish what you are asking for.

For example, a YESTERDAY object would be easy to do as:

SYSDATE - 1

However, this does not take into account weekends. Yesterday for Sunday would be defined as Saturday. Yesterday for Monday would be defined as Sunday. If you want “yesterday” for Monday to be Friday, then you have to get a little fancier. Something like:

DECODE(TO_CHAR(some_date,‘DAY’), ‘MONDAY’, SYSDATE-3, SYSDATE-1)

This will decode the day name of a date. If the day name is MONDAY, then the formula will give you the previous Friday’s date as “yesterday”. For any other day (including Sunday) it will return the true “yesterday”. You can add to this to adjust for Sunday as well if you like.

To do Last Week it gets a little more complicated. Something that I have used in the past goes something like this:

some_date BETWEEN NEXT_DAY(TRUNC(SYSDATE-8), ‘SUNDAY’) and NEXT_DAY(TRUNC(SYSDATE-2),‘SATURDAY’)

This formula will allow you to check for a date between last Sunday and last Saturday. No matter which day this week you run the query, the date range will be from Last Sunday to Last Saturday.

The Last Month object requires the ADD_MONTHS() function. You can build a month range something like:

some_date between LAST_DATE(ADD_MONTHS(sysdate, -2))+1 and LAST_DATE(ADD_MONTHS(SYSDATE, -1))

This formula will get you the last date from two months ago and add one, giving you the first date of last month. The other part of the function gets the last day from the previous month.

There is more than one way to do these various formulas, but these should work for you, or at least get you started!

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

system · December 28, 1998, 4:35am

In a message dated 98-12-26 23:52:16 EST, you write:

I would like to be able to use relative dates as conditions for date fields.
For instance, I may want to see all invoices dated or

or or , etc… etc… I do not want to have to
hard
code dates into the conditions every time I want to use them.

Hi, Lisa…

You can do a variety of tricks with objects to accomplish this. If memory
serves, you are using the Oracle database. You can use objects that reference
the SYSDATE value to accomplish what you are asking for.

For example, a YESTERDAY object would be easy to do as:

SYSDATE - 1

However, this does not take into account weekends. Yesterday for Sunday would
be defined as Saturday. Yesterday for Monday would be defined as Sunday. If
you want “yesterday” for Monday to be Friday, then you have to get a little
fancier. Something like:

DECODE(TO_CHAR(some_date,‘DAY’), ‘MONDAY’, SYSDATE-3, SYSDATE-1)

This will decode the day name of a date. If the day name is MONDAY, then the
formula will give you the previous Friday’s date as “yesterday”. For any other
day (including Sunday) it will return the true “yesterday”. You can add to
this to adjust for Sunday as well if you like.

To do Last Week it gets a little more complicated. Something that I have used
in the past goes something like this:

some_date BETWEEN NEXT_DAY(TRUNC(SYSDATE-8), ‘SUNDAY’)
and NEXT_DAY(TRUNC(SYSDATE-2),‘SATURDAY’)

This formula will allow you to check for a date between last Sunday and last
Saturday. No matter which day this week you run the query, the date range will
be from Last Sunday to Last Saturday.

The Last Month object requires the ADD_MONTHS() function. You can build a
month range something like:

some_date between LAST_DATE(ADD_MONTHS(sysdate, -2))+1
and LAST_DATE(ADD_MONTHS(SYSDATE, -1))

This formula will get you the last date from two months ago and add one,
giving you the first date of last month. The other part of the function gets
the last day from the previous month.

There is more than one way to do these various formulas, but these should work
for you, or at least get you started!

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

system · December 28, 1998, 5:16pm

I would like to be able to use relative dates as conditions for date fields.

lisa:
is your intention truly to look for ‘yesterday’ (the day before today) or the ‘last business day?’ the latter is a little trickier, as our current holiday schedule will attest!

this means that any formula to skip saturday and sunday as a ‘non-business day’ will also need to skip ‘Christmas Day on Friday’ (or any other day of the week). naturally, this assumes you intend to skip holidays. you may also want to skip ‘plant shutdown week’ in some manufacturing environments. perhaps there some local days off unique to a state or province that you may have to account for.

one option is to have a date table that contains all the pertinent info for each day of the year. it can contain the week it belongs to, thus allowing you to determine all the days that belong to the prior week (week minus 1, unless it rolls into the prior year!). it can contain the same info for month, quarter, etc.

a dba can then create views, or actual tables, that contain the relative info for you. the designer can then create objects based on those tables/views.

frank b. duncan
national city corp.
216 488 7677

Listserv Archives (BOB member since 2002-06-25)

system · December 28, 1998, 5:16pm

 >I would like to be able to use relative dates as conditions for date
 >fields.

 lisa:
 is your intention truly to look for 'yesterday' (the day before today)
 or the 'last business day?'  the latter is a little trickier, as our
 current holiday schedule will attest!

 this means that any formula to skip saturday and sunday as a
 'non-business day' will also need to skip 'Christmas Day on Friday'
 (or any other day of the week).  naturally, this assumes you intend to
 skip holidays.  you may also want to skip 'plant shutdown week' in
 some manufacturing environments.  perhaps there some local days off
 unique to a state or province that you may have to account for.

 one option is to have a date table that contains all the pertinent
 info for each day of the year.  it can contain the week it belongs to,
 thus allowing you to determine all the days that belong to the prior
 week (week minus 1, unless it rolls into the prior year!).  it can
 contain the same info for month, quarter, etc.

 a dba can then create views, or actual tables, that contain the
 relative info for you.  the designer can then create objects based on
 those tables/views.


 frank b. duncan
 national city corp.
 216 488 7677

Listserv Archives (BOB member since 2002-06-25)

system · December 28, 1998, 10:24pm

Hi Lisa

You can do this in many ways.

Basically you have to create objects for YESTERDAY, TODAY, LastWeekStartDate, LastWeekEndDate and so on…

These Objects can be populated by using

BO Script (using @SCRIPT command) or
Database stored procedures (In object definition you can refer to DB stored procedure
which returns the
expected date) or
directly writing the formula in object definition.

If you don’t need complex calculations (like you don’t bother about holidays) then 3rd option is better. I’d choose option 2 when need to code complex calculations.

Now you can create your report. Pull to conditions panel.
Select the Operator and then select ‘Select an Object’ in place of operand
to select the Date objects you created.

– Vasan

I would like to be able to use relative dates as conditions for date fields.
For instance, I may want to see all invoices dated or
or or , etc… etc… I do not want to have to hard
code dates into the conditions every time I want to use them. I can do this
using filters, but by that time I’ve already retrieved EVERY invoice from the
database and brought it down to the client. I need to be able to do this in
either the Query panel, or (better yet) in the Designer module so I can reuse
them in many reports w/o having to redefine them.

We’re BO 4.1.2

Thanks!

Listserv Archives (BOB member since 2002-06-25)

system · December 28, 1998, 10:24pm

    Hi Lisa

    You can do this in many ways.

    Basically you have to create objects for YESTERDAY, TODAY,
    LastWeekStartDate, LastWeekEndDate and so on..

    These Objects can be populated by using
      1. BO Script (using @SCRIPT command) or
      2. Database stored procedures (In object definition you can refer

to DB stored procedure
which returns the
expected date) or
3. directly writing the formula in object definition.

    If you don't need complex calculations (like you don't bother about
    holidays) then 3rd option is better. I'd choose option 2 when need
    to code complex calculations.

    Now you can create your report. Pull <Invoice Date> to conditions

panel.
Select the Operator and then select ‘Select an Object’ in place of
operand
to select the Date objects you created.

    -- Vasan

I would like to be able to use relative dates as conditions for date
fields.
For instance, I may want to see all invoices dated or

or or , etc… etc… I do not want to have to
hard
code dates into the conditions every time I want to use them. I can do
this
using filters, but by that time I’ve already retrieved EVERY invoice from
the
database and brought it down to the client. I need to be able to do this
in
either the Query panel, or (better yet) in the Designer module so I can
reuse
them in many reports w/o having to redefine them.

We’re BO 4.1.2

Thanks!

Listserv Archives (BOB member since 2002-06-25)

system · August 2, 2004, 2:37pm

This is great information and something that I am trying to implement myself. My question is does anyone know how this could be implemented using DB2 for IBM390?? I don’t have any experience with this database and from my research, it seems this isn’t as straightforward on this platform.

Any help is appreciated,
Shelley

Shelley (BOB member since 2003-09-15)

system · August 5, 2004, 2:21pm

Sorry to bring up an old topic…

I need some_date to equal the last Monday of the:

Previous month
Previous week and then
Current Monday when ran Monday through Sunday.

This could be 3 predefined conditions or 3 different date objects.

Any help would be great!!!

I am using a Teradata DB.

Thank you

vdog_2000 (BOB member since 2002-11-14)

system · August 5, 2004, 2:55pm

See my post at the end of this topic: Issue with the Year

JP Brooks (BOB member since 2002-10-22)

system · August 5, 2004, 3:30pm

I agree that DataBase changes are the best…but there is way to much red tape to go through to get that done. So I am looking at any other options.

Any other ideas???

Thank you.

vdog_2000 (BOB member since 2002-11-14)

system · August 6, 2004, 12:51pm

Vernon,
Try looking at my post entitled Date Prompts In Reports (https://bobj-board.org/t/31743) (I’m not sure how to post a link to it It was posted under Designer.

This was implemented for DB2, but it shouldn’t be too difficult to change the syntax for Teradata.

Hope this helps,
Shelley

Shelley (BOB member since 2003-09-15)