RE: Date conditioned SQL Select

Umm.  Now that I think about it, the result could be incorrect if user
enter 30990912.  I hate to have to hardcode but I think you are going to
have to this time.  Try this:
select * from myFile
 where                                           
   CYYMMDD = 
      (case 
         when substr(digits(ccyymmdd),1,2) = '20'
           then dec('1' concat substr(digits(ccyymmdd),3,6),7,0)       
         when substr(digits(ccyymmdd),1,2) = '19'
           then dec('0' concat substr(digits(ccyymmdd),3,6),7,0) 
         else 
           99999999
       end)   
 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Lim Hock-Chai
Sent: Tuesday, September 12, 2006 9:01 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Date conditioned SQL Select

Try below:
select * from myFile
 where                                           
   CYYMMDD = 
      (case when year(curdate()) = int(substr(digits(ccyymmdd),1,4))
         then dec('1' concat substr(digits(ccyymmdd),3,6),7,0)       
         else dec('0' concat substr(digits(ccyymmdd),3,6),7,0) 
       end)   

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of klerpg@xxxxxxxxxxxxx
Sent: Monday, September 11, 2006 12:18 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Date conditioned SQL Select

All:

I have a file that I need to read based on a user-input date field
(CCYYMMDD). The file field I am comparing to is a 7 digit date field
(CYYMMDD). 

In SQL, how do I get the select statement to pull records based on a
comparison of these two fields?

TIA

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.