RE: Date Variables in CL

[rob@xxxxxxxxx]

I tried playing around with this a little also.  Although I'd take Charles 
suggestion and modify it slightly
select * from vinlog
where days(current date) - days(CvtNumericToDate(IJDATE)) > 180

Sql seems to have a real problem casting a numeric date into a date field. 
 Hence the UDF suggestion.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





CWilt@xxxxxxxxxxxx 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/28/2004 03:49 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Fax to

Subject
RE: Date Variables in CL






Tim,

Take a look at the CVTDAT command.

If you decide to use SQL, you'd want to write a CvtDateToNumeric User
Defined Function (UDF)

select * from VINLOG
where IJDATE <= CvtDateToNumeric(CURRENT_DATE - 6 MONTHS)

HTH,
Charles 

Charles Wilt
iSeries Systems Administrator / Programmer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: Tim Ellifritz [mailto:tim@xxxxxxxxxxxxxxxxxxxxxxxxx]
> Sent: Thursday, October 28, 2004 3:58 PM
> To: Midrange-L@Midrange. Com
> Subject: Date Variables in CL
> 
> 
> Hi,
> 
> A newbie here...messing with CL...please let me know if I'm 
> in the wrong
> list.
> 
> I want to select all records from file VINLOG where IJDATE is 
> 6 months ago
> or later and IJTYPE is 1D, then insert them into QTEMP/CSDS. 
> IJDATE format
> is ccyymmdd (ex. 20041028)
> 
> Would I ??
> 
> RTVSYSVAL  SYSVAL(QDATE) RTNVAR(&QDATE)
> 
> 
> Then the part I need help with I think, for below I need &CYMD to be
> 20040428, April 28th??? How can I do that?
> 
> OVRDBF     FILE(VINLOG) SHARE(*YES)
> OPNQRYF    FILE((VINLOG)) QRYSLT(' IJDATE *GE ' || +
>              &CYMD || ' *AND IJTYPE *EQ ''1D'' ')
> 
> CPYFRMQRYF FROMOPNID(VINLOG) TOFILE(QTEMP/CSDS) +
> MBROPT(*ADD) CRTFILE(*YES)
> 
> Or would it be easier with SQL? An example of that would be 
> appreciated as
> well...
> 
> Thanks,
> Tim
> 
> 
> 
> --
> 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.