|
From Al Macintyre on BPCS 405 CD > From: Andreas.KOPP@de.imp-group.com (Andreas) > > I have a question concerning Query400. I suggest you visit some 400 forums that are ERP-independent, for a variety of tips & techniques using 400 tools, such as: http://news400.com/ http://www.midrangecomputing.com/scripts/webboard.dll/mainmenu http://www.forums400.com http://www.datamation.com/discussions/index.html next 2 lines need to be re-connected http://discussions.earthweb.com/cgi-bin/dnewsweb?cmd=article&group=earthweb.ap pdev.general&item=10&utag= > I have a field in format date (DD/MM/YY) and I would like to create a new > field containing the original date plus 1 day. > > How can I add a day to date? > Is this possible? Our BPCS data base fields containing dates are in the format CCYYMMDD which is generally a valid IBM ISO date, but not defined as a date type, because BPCS tolerates non-date data in those fields, such as all zeros or all nines, so your selection criteria needs to exclude such data from your query. You may need to check the IBM Job Date format, since we have figured out how to have a different default date format in Query than in BPCS but I do not remember what we did. If you want to rearrange contents of fields, as we often do, to print dates in format other than CCYYMMDD, we can only substring & concatenate fields that have been converted to alphameric format, such as Alpha_Result-Field_Name = DIGITS(Numeric_Field_Name) After the manipulation, we can get the data back to numeric format using INT to integer or DECIMAL to decimal, provided we do not have got non-numeric data in there inviting decimal data error. Thus, you can deconstruct a date into pieces, add or subtract 1 day to a piece, but is the result a valid date or Feb the 30th or did you start with 1st day of a month ... converting vs. your own estimated calendar can be messy - much cleaner to get it into IBM date type & learn the relevant terminology to let IBM/400 do the calendar math. Today = CURRENT(DATE) ... this returns today's date in IBM data type of a date Date_Result = DATE(BPCS field with date data in right format) ... this takes some BPCS field name & makes it a date from the perspective of IBM data types Day_Result = DAY(BPCS date field) ... so for example if the date field is 19991105 this returns the value 05 & considers it to be a DAY for purposes of IBM date math Same deal with MONTH & YEAR ... this way you can extract pieces of a date instead of the sub-string concatenation route. Once you have defined a date as a date in IBM terms you can do date math Days_Result = DAYS(BPCS field representing a date) ... this gets you number of days since Jan 1 0001 for purpose of subtracting 2 such dates to get # days in between, or adding fixed numbers to this work value. An example of this might be Work_Days(DAY) = DAYS(TSDTE) - DAYS(TTDTE) in which the result field is expressed as a day for date math purposes & consists of the difference between 2 BPCS dates, then later you can add Work_Days to some other date & the result will be a correct calendar date - you need not line up what field layout or futz with number of days in which month. We are a bit sloppy with our Queries, sometimes using things like (YEAR1-YEAR2)*360 + (MM*30) - (DD*-1) There is on-line help for Query/400 which I do not find to be very helpful. Instead, my bible is the IBM Query Users manual SC41-4210-00 & some stuff that we got from our ERP consultants ... we have a set of references that correlate BPCS file naming with standard query/400 functions ... you need to get this sort of thing directly from your ERP consultants since ours is intellectual property protected. When we leave our dates in CCYYMMDD format, we generally use edit word ' - - ' because the edit codes drop off CC, for our version of OS/400. Feel free to contact me off-BPCS_L to pursue this further ... I could real-mail you one of our query specifications which has date math similar to what you are looking for, so you could copy the technique directly. No guarantees that we are working this correctly of course. 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.