Hi Bill,
TIMESTAMP_FORMAT function became available in V6R1.
Nice to see you using SSRS. I'm a big fan of that product.
As Birgitta mentioned, use of functions in certain places of a query, in
particular but not limited to a WHERE clause, can hurt performance.
As Kevin Bucknum mentioned, a date cross reference file can be used instead
in places where use of a function hurts performance.
Here is an SQL script to create a date cross reference file, that I'm
fairly sure will run on V5R4. Replace ?OUTPUT_SCHEMA in this script before
running it with the schema in which to create the table and indexes.
The table created below could easily be enhanced with additional columns
like day of week, holiday flag, a YYYYMMDD format date, etc.. When you
have a significant volume of CYYMMDD dates that you need in ISO format,
JOIN to this table on column NUM7_DATE using your CYYMMDD date to fetch the
ISO_DATE equivalents.
I use a date conversion table like this all the time to convert YYYYMMDD
dates to ISO format during database migrations. This table is created with
CYYMMDD dates instead of YYYYMMDD, in the range between 1900-01-01 through
2099-12-31.
create table ?OUTPUT_SCHEMA.DATE_XREF as (
with recursive
DATE_RANGE_ISO as (
select date( '1900-01-01' ) as MIN_DATE_ISO
,date( '2099-12-31' ) as MAX_DATE_ISO
from ( values( 1 ) ) as T
)
,DATE_RANGE_BOTH as (
select MIN_DATE_ISO
,MAX_DATE_ISO
,dec( int( replace( VARCHAR_FORMAT( TIMESTAMP_ISO(
MIN_DATE_ISO ), 'YYYY-MM-DD' ), '-', '' ) ) - 19000000, 7, 0 ) as
MIN_DATE_NUM7
,dec( int( replace( VARCHAR_FORMAT( TIMESTAMP_ISO(
MAX_DATE_ISO ), 'YYYY-MM-DD' ), '-', '' ) ) - 19000000, 7, 0 ) as
MAX_DATE_NUM7
from DATE_RANGE_ISO
)
,DATES_IN_RANGE ( NUM7_DATE, ISO_DATE ) as (
select MIN_DATE_NUM7 as NUM7_DATE
,MIN_DATE_ISO as ISO_DATE
from DATE_RANGE_BOTH
union all
select dec( int( replace( VARCHAR_FORMAT( TIMESTAMP_ISO( ISO_DATE
+ 1 day ), 'YYYY-MM-DD' ), '-', '' ) ) - 19000000, 7, 0 ) as NUM7_DATE
,ISO_DATE + 1 day as ISO_DATE
from DATES_IN_RANGE
cross join DATE_RANGE_ISO
where ISO_DATE + 1 day <= MAX_DATE_ISO
)
select * from DATES_IN_RANGE
)
with data
;
create index ?OUTPUT_SCHEMA.DATE_XREF1
on ?OUTPUT_SCHEMA.DATE_XREF ( NUM7_DATE, ISO_DATE )
;
create index ?OUTPUT_SCHEMA.DATE_XREF2
on ?OUTPUT_SCHEMA.DATE_XREF ( ISO_DATE, NUM7_DATE )
;
Mike
date: Wed, 29 Jun 2016 09:20:54 -0400
from: Bill Howie <blhowie66@xxxxxxxxx>
subject: An easy (I think) SQL question
All,
A couple of things I probably should have mentioned initially.......first,
I'm only using SQL to accomplish this, not an RPG program. What I'm
actually doing is writing an SQL script in SSRS but accessing an AS/400
database. So basically the script has to work on the AS/400, but I'll
actually be running it within SSRS. Is the TIMESTAMP_FORMAT function
something that came along with more current releases of the OS? We are,
unfortunately, still on V5R4.
Bill
midrange.com
