RE: [EXTERNAL] records between two dates

Or, write a UDF to do the conversion. Be sure to specify it as "deterministic" to aid in performance.

For clarity, I much prefer UDF's over calcs like this.


Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Shore via MIDRANGE-L
Sent: Thursday, June 30, 2022 9:27 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: [EXTERNAL] records between two dates

Combine the field together in the following manner
((CC * 1000000) + (YY * 10000) + (MM * 100) + DD)

Alan Shore
Solutions Architect
IT Supply Chain Execution

[NHScsignaturelogo]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx

'If you're going through hell, keep going.'
Winston Churchill

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of jerry ven
Sent: Thursday, June 30, 2022 12:01 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] records between two dates

Hi,

How to filter records from a date field if it's split in CC YY MM DD (
Each field here with 2 length and decimal data type) using SQL query from
a file?

i mean date field is like in file1 like below:-

fld1 fld2 fld3
fld4
CC YY MM
DD


So from this type of split fields how can we filter records for a specific
week, month, year and century ,and records between specific dates using
SQL query on this file1 ?



Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=m7dwPEgNWqV4UWa%2FBQ178kyqBaLWixJfIfIVNJ2by7o%3D&amp;reserved=0<https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=m7dwPEgNWqV4UWa%2FBQ178kyqBaLWixJfIfIVNJ2by7o%3D&amp;reserved=0>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=EZwt75YdXWAlkv7CO27ZGxpmhrdfkegWElWL5xhPSPY%3D&amp;reserved=0<https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=EZwt75YdXWAlkv7CO27ZGxpmhrdfkegWElWL5xhPSPY%3D&amp;reserved=0>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&amp;data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=tUlTmPYWZozfUnZuYUZdIqtwgTyfLbieenJ3q6UaAAs%3D&amp;reserved=0<https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&amp;data=05%7C01%7C%7C034c6b1bda994ce2fd4508da5ab5557a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637922032110632274%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=tUlTmPYWZozfUnZuYUZdIqtwgTyfLbieenJ3q6UaAAs%3D&amp;reserved=0>