Re: SQL Date function question

Just for reference: We use a product named IQ. Personally, for quick reports, I fall back on WRKQRY. However, the IQ product includes a calendar table. It would seem counter productive, due to the multiple indexes. However, it is more practical then using multiple mapped fields to transform a date into whatever format is required for record selection.

Is it perfect? No. But, neither is the data in the files.

John McKee

-----Original message-----
From: rob@xxxxxxxxx
Date: Fri, 08 Jul 2011 06:47:43 -0500
To: Midrange Systems Technical Discussion midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Date function question

I "assumed" that the article was going to be one of those suggestions to
do something like

MYFILE
KEYFIELD
DATAFIELD1
DATAFIELD2
...
DateFieldNbrmmddccyy
...

and create a related table
MYFILE2
KEYFIELD
DateFieldForReal
OtherNewFieldsAsNeeded
...

But it wasn't. It was more of a load a new, unrelated table for use by
all tables using unreal dates. This new table may have columns like
RealDate
DateNbrCCYYMMDD
DateNbrMMDDCCYY
DateNbrJulian
DateNbr...
DateChar...
Then you preload the above table with all dates from 01-01-0001 through
maybe two hundred years into the future.

Then you would do a
Select KEYFIELD, DATAFIELD1, RealDate, DayOfWeek(RealDate)
From MYFILE left outer join DateTable on DateFieldNbrmmddccyy =
DateNbrMMDDCCYY

Of course, you could also have DayOfWeek in that table and avoid that
calculation also. Then there's month, etc. And if your shop has unique
stuff like fiscal period and whatnot you can cover that also.

And the pundits are saying that joining to the additional table may out
perform doing the calculations on the fly. Very interesting!

values days(date('2400-12-31'))
returns 876,582. So that's how many rows you might put into your table.
And probably all columns would have their own index

Rob Berendt
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Luis Rodriguez <luisro58@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 07/06/2011 08:37 PM
Subject: Re: SQL Date function question
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Jon,

Thanks for the link. It is not an obvious solution (maybe we are hardwired
to avoid creating extra tables) but, once you see the simplicity of it, it
seems the right way to go.

Thanks again,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Wed, Jul 6, 2011 at 7:27 PM, Jon Paris <jon.paris@xxxxxxxxxxxxxx>
wrote:

Check out the SQL date reference in today's blog post Brad - personally

I'm

never going to bother wrestling with dates in SQL again. We'll be

writing

it up fully later but everything you need in that Redbook.

http://ibmsystemsmag.blogs.com/idevelop/2011/07/jon-and-susan-get-educated.html

On Jul 6, 2011, at 5:06 PM, midrange-l-request@xxxxxxxxxxxx wrote:

I've got a program that uses dynamic SQL and I'm having a hard time
finding documentation for this, or to find out if it's even possible.

The file contains a date in the format MMDDCCYY. I need to compare
the year entered in a query with the year in the date.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.