Robert,
My quote refers to the fact that the BETWEEN operator will no longer work correctly in case of a time part:
e.g. assume, that a “table.datefield” has the value of “17-11-1999 09:58:12 PM”.
To get rid of the time part apply the trunc() function, in the above example you will get “17-11-1999 00:00:00”.
Applying the condition mentioned in my mail will not retrieve rows from the database where the table.datefield has the same date as the end-date in the condition, but has a time > 0, therefore it is > end-date. Since the end-date of the condition (implicitely) has no time part, i.e. “end-date 00:00:00”, all rows with datefield values of “end-date something” will not be retrieved, since they are > end-date.
What you can do is the following:
- You can use the trunc() function to truncate the time part from “table.datefield”: trunc(table.datefield) truncates to 00:00:00 on the same day and use conditions which guarantee the correct handling of the end-values.
The condition then might look like:
trunc(table.date_field) BETWEEN
to_date(@prompt(‘Enter date (MM/YYYY)’,‘A’,), ‘MM/YYYY’) - @prompt(‘Number of months?’,‘N’,) + 1
AND
to_date(@prompt(‘Enter date (MM/YYYY)’,‘A’,), ‘MM/YYYY’)
which gives you exactly what you need, but has the disadvantage of loosing all index acesses on the datefield column.
- Another way is to not use the BETWEEN operator, instead use the following:
table.date_field >= to_date(@prompt(‘Enter date (MM/YYYY)’,‘A’,), ‘MM/YYYY’) - @prompt(‘Number of months?’,‘N’,) + 1
AND
table.date_field < to_date(@prompt(‘Enter date (MM/YYYY)’,‘A’,), ‘MM/YYYY’) + 1
This should also work and give you the right results.
hope this helps
Walter
BTW: Oracle handles date values very conveniently: You an create a datetime value by simple arithmetic: internally the values are stored as fractions of days, e.g. '12-01-2000 13:38:00" can be created by assigning the value “12-01-2000” + 13/24 + 38/(24*60) to a datetime variable.
Differences work the same way: whenever (enddate - startdate) gives you a fraction, this is a fraction of a day, e.g. “21-02-2000 01:28:00 AM” - “20-02-2000 02:28:00 AM” will give you something like 0.958333, which is exactly 23/24.
“Duindam, R. (Robert)” wrote:
Hi listers,
Walter wrote:
table.date_field BETWEEN
to_date(@prompt(‘Enter date (MM/YYYY)’,‘A’,), ‘MM/YYYY’) - @prompt(‘Number of months?’,‘N’,) + 1
AND
to_date(@prompt(‘Enter date (MM/YYYY)’,‘A’,), ‘MM/YYYY’)
Attention has to be paid if the date field also contains a time part…
to answer to question from of Steve:
—Steve Krandel wrote:
I need your advice on the following issue:- The user should be prompted to enter year and month. The report should display the revenue for the past 12 months from the month year entered.
Walter,
I’m very much intrested in your quote “Attention has to be paid if the date field also contains a time part…”. Because this is just my problem.
I our oracle 8 database dates are stored in ‘dd-mm-yy hh:mm:ss AM’ format. We know that with the to_char function we can transform this format to dd-mm-yyyy. But then you can’t properly sort the date-column anymore. We could use the yyyy-mm-dd format, which sorts properly of course, but we would like to see that the user, when prompted, can type the date in dd-mm-yyyy format. This format is the most common format used in the Netherlands.
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.
–
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
Listserv Archives (BOB member since 2002-06-25)