Re: show all values between X and Y

Stacy Y wrote:
> Hi there,
>
> I am working on a reporting project where all datefields have been set
> as varchar2 fields, so computations on dates are proving to be quite
> tricky.
>
> I have a create date and a completion date, and I need to find the
> number of days between those two dates, not including off days
> (weekends and holidays). The weekend and holiday dates are stored in a
> separate table, so I can reference back to them (i.e: NOT IN (SELECT *
> FROM TABLE).
>
> Can anyone help me with the way this statement would look? I'm stuck.
> This is the line for what I have so far - I don't know how to
> incorporate that reference into it...
>
> SELECT
> SUBSTR(CAD.AEVEN.XDTS,-16,8)-SUBSTR(CAD.AEVEN.CREATE_DATETIME,-16,8) AS
> ELAPSED_DAYS
>
> Thanks!
> Stacy

One of the first rules in design is to use the most appropriate database data type to store the actual data. If the data represents a date then it should be stored as a date.

The function suggested probably needs error handling for when the character column that is supposed to represent a valid date turns out not to hold a valid character representation of a date.

If you absolutely have to keep the data in a varchar2 column then you might want to consider either adding an additional column with the date datatype for the data and/or creating FBI, function based indexes, on some of the character date columns. You are bound to have a few queries that would benefit from having an index but depending on what format was chosen for the character representation just adding an index on the existing column may not help.

HTH -- Mark D Powell -- Received on Tue Sep 12 2006 - 12:28:53 CDT