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

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.