Problem with dates NEW QUESTION2

building semantic layer
1

Hi

BO5, Oracle 8.0

All this prompts me to ask:-

How can I get the date to display as MM/YYYY in list of values but still behave as a date in comparisons etc.

Jonathan Bliss, Product Consultant
McKessonHBOC Computer Centre, Harold Wood Hospital site, Gubbins Lane, Romford, RM3 0NE
mobile:0498 668084, tel 01708 336135 (direct line with voicemail), fax 01708 376270

In a message dated 00-01-26 06:58:11 EST, you write:

So Walter, do you or anyone else know a way to get rid of
the
hh:mm:ss AM and still use the object as a date-field with format
dd-mm-yyyy.

We use BO 5.01 en Net 8.

Regards,
Robert Duindam

Simple: use trunc(date-field).

The command trunc() returns the date value as a date without any time
element. Well, that’s not exactly true. A date field in Oracle always has a
time element. The trunc() command simply sets the time value to midnight.
That way for any comparison (equal, between, greater than, etc.) you don’t
have to worry about time values getting in the way.

Caveat: using any function on an indexed value will cause that index to be
ignored. So if you have an index on date_field, and try the following query:

select whatever
from wherever
where trunc(my_date) = trunc(sysdate)

any index on foo_date will not help.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

2

How can I get the date to display as MM/YYYY in list of values but still
behave as a date in comparisons etc.

Jonathon – Can you use “Edit Values” for the LOV? Then format the date as you wish in the LOV.

I haven’t tried this, so let me know if it works.

Judy Brown
QuadraMed

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

3

Jonathan> How can I get the date to display as MM/YYYY in list of values but still
behave as a date in comparisons etc.

Judy>Can you use “Edit Values” for the LOV? Then format the date as you wish in the LOV.<

I have never seen a way to do this in edit values, and using formatting SQL such as to_char obviously stops them acting as a comparative. Changing the date object to a to_char works by making the LOV and the object the same but then I cannot use it as a date in other queries and I reckon it will slow the queries as I think the index on this field would no longer be used. I could have two objects one for each purpose but only the most experienced users would understand why, and they can cope with date:time LOVs anyway.

Jonathan Bliss, Product Consultant
McKessonHBOC Computer Centre, Harold Wood Hospital site, Gubbins Lane, Romford, RM3 0NE
mobile:0498 668084, tel 01708 336135 (direct line with voicemail), fax 01708 376270

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