On 01-Jan-2016 10:14 -0700, Booth Martin wrote:
<<SNIP>> I have a field in a data file which is 7/0 and is cyymmdd.
Presumed, that the above intends to suggest the column is typed as
either NUMERIC(7, 0) or DECIMAL(7, 0)
I want to use it as a date field so I can get the week. Presently
that is being done by substringing the digits and replacing the
leading "1" with a "20". That seems cumbersome to me but maybe that's
the only way to do it?
As with most software programming tasks, there are many ways.
The numeric expression cast to character using DIGITS and CONCAT to
derive the DATE value, as offered by Birgitta in response to the same
quoted message, is probably the /nicest/ expression to use. The use of
a scalar User Defined Function (UDF) is likely to be considered even
/nicer/, but with the caveat that a derived index could not be created
to match.
Yet given the specific expression for "substringing the digits and
..." was not offered, with which to compare, how would a reader know if
their character string expression might be perceived as any better?
Additionally, not knowing the constraints on the date-like numeric data,
the reader does not know if there might be simplified methodologies to
effect what is desired.
Given constraints on the CYYMMDD data representing valid date values
between 1910-01-01 and 2099-12-31, having been stored only as positive
values, then the following are expressions that should suffice; the
latter depends upon my [perhaps false] recollection that the resolution
of the WHEN expressions in the CASE Expression reflects the order of the
specifications, such that the overlapping logic suffices versus having
to explicitly use ranges to ensure the desired effect:
date( case when CYYMMDD < 1000101 then '19' /* 1900s */
else '20' /* 2000s */
end
concat right(CYYMMDD, 6) concat '000000'
) as DATE_TYPED_EXPRESSION
date( case when CYYMMDD >= 1000101 then '20' /* 2000s */
when CYYMMDD >= 0100101 then '19' /* 1910+ */
end /* else NULL for pre-1910 dates */
concat right(CYYMMDD, 6) concat '000000'
) as DATE_TYPED_EXPRESSION
midrange.com
