× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.