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.
midrange.com
