Re: AW: Embedded SQL formatting dates

On 1 January 2016 at 12:14, Booth Martin <booth@xxxxxxxxxxxx> wrote:

I have a field in a data file which is 7/0 and is cyymmdd. 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 thats the only way to do it?

Sometimes it seems as if SQL DML can do anything! Maybe I'm
officially an old fogey now, but I still prefer to have my RPG code
handle data conversions from whatever raw input I have to the
normalised columns that SQL demands. For date conversions, Alan's
iDate is a winner.

Mike's comment about 'error spamming the job log' made me chuckle, but
its an interesting point to consider when I'm converting 14M input
rows, each having 5 decimal fields which I need to convert to a date.
The 'date table' technique has been discussed before, and is a useful
way to convert between multiple formats (as many as one puts in each
row of the table) and can be used to indicate things like corporate
holidays, weekends, work days, etc. Charles has given some very nice
examples in the past.
--buck