Date formula

I have a report that contains the open date for a work order. I want to add a column that will display the number of days since the work order was opened (current date - open date). How do I calculate this? I tried using the “daysbetween” formula but kept getting an “invalid data type” message. By the way, the format of the open date is mm/dd/yy.

Any help is greatly appreciated.

Dave


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

Well,

  1. You can create an object in designer which says exactly what you wrote. Depending on the backend that you use the syntax may vary but for example in oracle you’d simply put (sysdate-). You can reference that in your reports.

  2. You can do pretty much the same thing but at the report level by creating a new variable.

  3. If you are getting invalid formats when trying this try wrappering your date fields with ToDate(CurrentDate)-ToDate()

CaddellD@PRISMPLUS.COM 03/06/00 01:01PM >>>
I have a report that contains the open date for a work order. I want to add a column that will display the number of days since the work order was opened (current date - open date). How do I calculate this? I tried using the “daysbetween” formula but kept getting an “invalid data type” message. By the way, the format of the open date is mm/dd/yy.

Any help is greatly appreciated.

Dave


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

In a message dated 00-03-06 15:01:48 EST, you write:

I have a report that contains the open date for a work order. I want to add
a column that will display the number of days since the work order was opened (current date - open date). How do I calculate this? I tried using the “daysbetween” formula but kept getting an “invalid data type” message. By the way, the format of the open date is mm/dd/yy.

If is really a date field, then the format doesn’t matter. However, the syntax should be:

=DaysBetween(, CurrentDate() )

CurrentDate() as a function requires the empty set of parenthesis.

Now, if is NOT currently a real date, then you must first convert it to a date value. Just because it looks like a date doesn’t mean that it is actually a date value. In that case, you need the ToDate() function.

=DaysBetween(ToDate(,“MM/DD/YY”, CurrentDate() )

That should work!

Regards,
Dave Rathbun
Integra Solutions
www.islink.com


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

Hello listers I need some help with a formula. I have an object in a universe that calculates the date from a juliean date. This is the formula:
to_date(‘2440588’, ‘J’) + ARADMIN.PT_TRACKING.TRANSFER_TIME / 86400 - @Select(Generic Date Objects\Daylight Savings Offset)

When I display the date that this formula me for example: 1/2/01 04:06 What I need is for this to be 01/02/01 04:06. I want to display the date in military time so I can do a substring and only pull in the hour.

Can this be done?
Thanks

Shirley Knight shkk@ti.com 972-927-6340
Business Objects support at Texas Instruments Inc.


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

Shirley said…

I have an object in a universe that calculates the date from a juliean date. This is the formula:
to_date(‘2440588’, ‘J’) + ARADMIN.PT_TRACKING.TRANSFER_TIME / 86400 - @Select(Generic Date Objects\Daylight Savings Offset)

Me… Have you played around with to_char(to_date(‘2440588’,‘J’),‘mm/dd/yy’)?

Cindy Clayton

Give people more than they expect and do it cheerfully. Talk slowly but think quickly.
Smile when picking up the phone. The caller will hear it in your voice. Mind your own business.
Learn the rules then break some.
Judge your success by what you had to give up in order to get it.


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

Cindy
Thank you so much, I was able to rework the formula and it works. Shirley


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