Difference in Dates

Hi, Listers;

I’m in BO 5.0.1, building a report against our Remedy database (Sybase) using the Remedy ODBC driver. I’m trying to get the difference between the date in the record, and the current date, in days. I tried:

=ToDate( ,“dd/mm/yy”)-ToDate( ,“dd/mm/yy”)

and

= -

Both give me a type mismatch error. Any ideas?

Michael Welter
Sr. Technical Analyst
AirTouch


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

If you are using the formula editor (rather than sql) use the daysbetween function.
Daysbetween(,)

Greg
2B||!2B

I tried:

=ToDate( ,“dd/mm/yy”)-ToDate( ,“dd/mm/yy”)

and

= -


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

Greg,

Thanks. That worked. However, I can’t seem to get it to display decimals. For example, instead of 2.25 days, it shows 2 days. Formatting the cell doesn’t seem to fix it. ANy more ideas?

Michael Welter
Sr. Technical Analyst
AirTouch


If you are using the formula editor (rather than sql) use the daysbetween function.
Daysbetween(,)


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

The DaysBetween() function in BusObj does not include time values. Which is why you won’t see decimals. Your choices are to do the date subtraction on the database, be happy without decimals, or use a really ugly formula on the client side to get the full time value (2.5 days, and so on).

If you are interested in seeing an example formula, there is one in the presentation I did two years ago at the 1998 BusObj conference. It was titled, “Variables and More Variables - The Sequel”, and can be found in Adobe Acrobat form on our web site at:

www.islink.com/bobjconf.htm

If you want to use a database function, check out datediff(). If my memory is correct, that’s the Sybase function used to return the difference between two dates. I would suggest that this is the better alternative.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

In a message dated 00-02-29 17:36:03 EST, you write:

Thanks. That worked. However, I can’t seem to get it to display decimals. For
example, instead of 2.25 days, it shows 2 days. Formatting the cell
doesn’t
seem
to fix it. ANy more ideas?

Michael Welter
Sr. Technical Analyst
AirTouch


If you are using the formula editor (rather than sql) use the daysbetween function.
Daysbetween(,)


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

“If you want to use a database function, check out datediff(). If my memory is correct, that’s the Sybase function used to return the difference between two dates. I would suggest that this is the better alternative.”

Dave,

I tried that, but since we’re hitting the database through Remedy, using the Remedy ODBC driver, the DateDiff() function is not available (of course, that didn’t stop me from trying it). I’ll probably go for the ugly formula solution. Thanks for your help.

Michael Welter
Sr. Technical Analyst
AirTouch


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