|
from Al Macintyre I am not as good as Bill at this, but different explanations & approaches can help identify what works with this logic. > From: cpapp@merck.com.ar (Claudia) > One more question: don't you know if there is a keyword in query that allow > me to convert an alphabetic field in a numeric ? (something opposite to the > keyword DIGITS). I have not used all Query/400 possibilities but believe this to be true Alpha_Result = DIGITS(numeric field type) Alpha_Result = CHAR(date or time type of field) Numbers_Again = DECIMAL(alpha field) returns decimal field Integers_Instead = INT(alpha field) > From: Andreas.KOPP@de.imp-group.com (Andreas) > Imagine the 17th of February is the shipping date of goods and we find this > date in the system. If we always calculate one day for carrying the goods > to the customer in this case the 18th of February will be the day he > receives the goods. You are fortunate in having shipments that are always only one day in transit. We have intercontinental trucking & international shipments that can get hung up in customs. > The date the customer receives the goods shall be displayed on the report. > > We already did the conversion from Format lsdte = 20000217 to lsdted = > 17/02/00 (as you stated in you previous mail). If this is a true IBM date (I have never worked with one with the slashes embedded or anything other than 8 digits), then date math should work ... if you add DD MM or YY fields, IBM will magically know where to add it with no fuss or bother. > Now I'm still not sure how to change the date to 18/02/00. ONE_DAY = DAY(+1) NEXT_DAY = LSDTED + ONE_DAY this takes 17/02/00 to 18/02/00 because if in fact you are working with DATE MATH then the rules are not the same as DECIMAL DIGITS MATH ... the trick is to be using fields defined as dates times durations or pieces of them, so that IBM gets the numbers into the right parts & recognizes calendar roll-over. > In our recent queries we were used to do it the "quick and dirty" way and > just said 20000217 + 1 and of couse in this case you'll get the 18st of > February but in another case it's also possible to get the 30st of > February.... MM/DD/YY like we use, or DD/MM/YY like you use - neither of those are a legal IBM date format, but while the date is in the BPCS format CCYYMMDD, it is a candidate for IBM date math, so add the 1 day when it is in that condition, then convert it with the 20 stripped off 2000 & with DD & MM rearranged with respect to YY. 1. Get BPCS date digits to IBM date format. IBM_DATE = DATE(LSDTE) At this point both LSDTE & IBM_DATE contain 20000217 except LSDTE is digits & IBM_DATE is true date format so date math can be used. 2. Add 1 day to result. Note that I do not want to add numeric + 1, I want to add ONE_DAY = DAY(+1) CYMD_ONE = IBM_DATE + ONE_DAY At this point the value is 20000218 defined as a date field 3. Get IBM date to break it up for rearange - it has to be character format before I can manipulate it CYMD_ALFA = CHAR(CYMD_ONE) DMY_ONE = SUBSTR(CYMD_ONE,7,2) || SUBSTR(CYMD_ONE,5,2) || SUBSTR(CYMD_ONE,3,2) At this point the value is 180200 defined alpha ... notice I did not insert the slashes like Bill did, because I like to convert back to digits & put them in with an edit word Al Macintyre ©¿© http://www.cen-elec.com MIS Manager Programmer & Computer Janitor http://www.whma.org = our nitch industry has a large committee searching for ErPdMes products right for WHM without work-arounds, which for many members resemble a snipe hunt - if you are in this software business & want to fix your product so it will not be mistaken for what your competition has been doing, then you might want to join our great game. +--- | This is the BPCS Users Mailing List! | To submit a new message, send your mail to BPCS-L@midrange.com. | To subscribe to this list send email to BPCS-L-SUB@midrange.com. | To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com. | Questions should be directed to the list owner: dasmussen@aol.com +---
As an Amazon Associate we earn from qualifying purchases.
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.