Re: Converting a numeric date value to a DATE in SQL

Chuck,
The reason that I wanted to convert the database field to a DATE data type
was so that I could subtract 1 day from the value to compare to the input
variable.
It just seemed that it would be easier to do that than convert my variable
to a date format, subtracting 1 then converting back to a decimal.

I was planning to use QMQRY to select my data and format it for a report.

Thanks,

Jeff Young
Sr. Programmer Analyst

On Tue, Sep 9, 2014 at 4:14 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 09-Sep-2014 09:38 -0500, Jeff Young wrote:

V6R1 system.
Using SQL, how do I convert a numeric value of a database field in
ISO date format (ccyymmdd) to a DATE data type for comparison?

As already suggested by John, for *comparison* purposes, the likely
better option is to *not* convert the numeric values of the database into
DATE data type; maybe conversion for the values in a result-set, but not
for the selection. When the numeric data is already in YYYYMMDD format,
many [if not most] /comparisons/ [esp. equivalence] are best achieved with
the data-type of the database; likely taking advantage of existing indexes,
rather than creation of an additional derived [expression] INDEX.

I need to select all records with a date that is equal to a value

input by the user.

There is surely little effort to convert the data taken as input into a
numeric value [in the form YYYYMMDD] to allow selection with a
compatible\matching data-type to that of the column in the TABLE.?

Given a numeric data-type column of the database file, a simple
equivalence is by far the most justifiable scenario for which any DATE data
type conversions can [and perhaps should] be avoided.

Is there any good justification that could be made for performing the
former, versus performing the [conspicuously simpler] latter, of the
following two examples [wherein the Dec8_Fld represents a DECIMAL(8, 0)
column, likely even having a keyed access path already defined]?:

select ...
where Cvt_Num_to_Date(Dec8_Fld)=DATE(:date_like_literal_input)

select ...
where Dec8_Fld=DECIMAL(:date_like_literal_input, 8, 0)

In the above two queries, the Convert Numeric To Date expression may
have to be performed on up to every row. In both of the above queries, the
Literal Input value need only ever be converted once; shown converted by
the casting scalars DATE and DECIMAL.

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.