RE: [U2] OCONV DATE ISSUE.
OCONV(@DATE, "DMZ") will remove the leading zero. Thanks, David A. Green www.dagconsulting.com (480) 813-1725 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lettau, Jeff Sent: Friday, February 08, 2008 11:24 AM To: u2-users@listserver.u2ug.org Subject: RE: [U2] OCONV DATE ISSUE. Thanks, I did OCONV(@DATE, 'DM')-0 to make the other date the same or I should answer that they are now both numeric. Rather than formatting. Data types changing behind the scenes are not always obvious when your working on something. Jeffrey Lettau ERP Systems Manager polkaudio -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Martin Phillips Sent: Friday, February 08, 2008 12:29 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] OCONV DATE ISSUE. Hi Jeff, > The question is when I do > OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not > 01. > Why? And how do I get these to match? OCONV returns a character string result. Using it in an arithmetic expression converts it to a number. Hence 02 minus 1 is 1, not 01. There are various ways to get what you want. One would be FMT(OCONV(@DATE, 'DM')-1, 'R%2') Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB +44-(0)1604-709200 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] OCONV DATE ISSUE.
THAT IS WHAT I WAS LOOKING FOR! Rather than doing this... IF OCONV(@DATE, 'DM') - 1 < '1' THEN OCONV(@DATE, 'DY') - 1 : OCONV(@DATE, 'DM') - 1 ELSE OCONV(@DATE, 'DY') - 1 : OCONV(@DATE, 'DM') - 1; (OCONV(Inv_So_Date, 'DY') : OCONV(Inv_So_Date, 'DM')-0); IF B$Record_Type = 'BK' THEN IF @1 = @2 THEN Qty ELSE '0' ELSE '0' I can do IF B$Record_Type = 'BK' THEN IF OCONV(ADD_MONTHS(@DATE,-13),'DYM') = OCONV(Inv_So_Date,'DYM') THEN Qty ELSE '0' ELSE '0' That is s much smaller. Jeffrey Lettau ERP Systems Manager polkaudio -Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Colin Alfke Sent: Friday, February 08, 2008 1:07 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] OCONV DATE ISSUE. In UD you can look at @MONTH and ADD_MONTHS(date,nbr) which should help having to do the gymnastics Martin and Bryan brought up hth Colin Alfke Calgary Canada -Original Message- From: Lettau, Jeff The question is when I do OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not 01. Why? And how do I get these to match? The reason for this is I'm trying to do this as a i-desc But my dates don't equal because of the weird oconv issue. IF OCONV(@DATE, 'DM') - 1 < '1' THEN OCONV(@DATE, 'DY') - 1 : '12' ELSE OCONV(@DATE, 'DY') : OCONV(@DATE, 'DM') - 1; (OCONV(Inv_So_Date,'DY'):OCONV(Inv_So_Date,'DM')); IF B$Record_Type = 'BK' THEN IF @1 = @2 THEN QTY ELSE '0' ELSE '0' I'll take a better solution if someone has it since I have to do this for 36 months making 36 i-desc to put it in columns for my output that will need to be grouped and summed. Jeffrey Lettau --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] OCONV DATE ISSUE.
Using the ADD_MONTHS function, as Colin suggested, will this work: IF OCONV(ADD_MONTHS(@DATE,-1),'Dym') = OCONV(Inv_So_Date,'Dym') THEN QTY ELSE 0 "Colin Alfke" <[EMAIL PROTECTED] .com> To Sent by: [EMAIL PROTECTED] cc stserver.u2ug.org Subject RE: [U2] OCONV DATE ISSUE. 02/08/2008 01:07 PM Please respond to [EMAIL PROTECTED] er.u2ug.org In UD you can look at @MONTH and ADD_MONTHS(date,nbr) which should help having to do the gymnastics Martin and Bryan brought up hth Colin Alfke Calgary Canada -Original Message- From: Lettau, Jeff The question is when I do OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not 01. Why? And how do I get these to match? The reason for this is I'm trying to do this as a i-desc But my dates don't equal because of the weird oconv issue. IF OCONV(@DATE, 'DM') - 1 < '1' THEN OCONV(@DATE, 'DY') - 1 : '12' ELSE OCONV(@DATE, 'DY') : OCONV(@DATE, 'DM') - 1; (OCONV(Inv_So_Date,'DY'):OCONV(Inv_So_Date,'DM')); IF B$Record_Type = 'BK' THEN IF @1 = @2 THEN QTY ELSE '0' ELSE '0' I'll take a better solution if someone has it since I have to do this for 36 months making 36 i-desc to put it in columns for my output that will need to be grouped and summed. Jeffrey Lettau --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] OCONV DATE ISSUE.
Thanks, I did OCONV(@DATE, 'DM')-0 to make the other date the same or I should answer that they are now both numeric. Rather than formatting. Data types changing behind the scenes are not always obvious when your working on something. Jeffrey Lettau ERP Systems Manager polkaudio -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Martin Phillips Sent: Friday, February 08, 2008 12:29 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] OCONV DATE ISSUE. Hi Jeff, > The question is when I do > OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not > 01. > Why? And how do I get these to match? OCONV returns a character string result. Using it in an arithmetic expression converts it to a number. Hence 02 minus 1 is 1, not 01. There are various ways to get what you want. One would be FMT(OCONV(@DATE, 'DM')-1, 'R%2') Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB +44-(0)1604-709200 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] OCONV DATE ISSUE.
>The question is when I do >OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not 01. >Why? And how do I get these to match? I saw this trick once. Might be adaptable. The idea is to make a string of month numbers and do an index. Only way I can think of to avoid creating/maintaining 36 i-descs is to write a program. S = '010203040506070809101112' PRINT SUBSTRINGS(S,INDEX(S,OCONV(DT,'DM'),1)-2,2) Brad U.S. BANCORP made the following annotations - Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. - --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] OCONV DATE ISSUE.
When you subtract one, the value of the variable gets cast as a number, effectively dropping the leading zero. Try this: FMT(OCONV(@DATE,'DM')-1,'2"0"R') Larry hiscock Western Computer Services -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lettau, Jeff Sent: Friday, February 08, 2008 8:35 AM To: u2-users@listserver.u2ug.org Subject: [U2] OCONV DATE ISSUE. The question is when I do OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not 01. Why? And how do I get these to match? The reason for this is I'm trying to do this as a i-desc But my dates don't equal because of the weird oconv issue. IF OCONV(@DATE, 'DM') - 1 < '1' THEN OCONV(@DATE, 'DY') - 1 : '12' ELSE OCONV(@DATE, 'DY') : OCONV(@DATE, 'DM') - 1; (OCONV(Inv_So_Date,'DY'):OCONV(Inv_So_Date,'DM')); IF B$Record_Type = 'BK' THEN IF @1 = @2 THEN QTY ELSE '0' ELSE '0' I'll take a better solution if someone has it since I have to do this for 36 months making 36 i-desc to put it in columns for my output that will need to be grouped and summed. Jeffrey Lettau ERP Systems Manager polkaudio Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] OCONV DATE ISSUE.
In UD you can look at @MONTH and ADD_MONTHS(date,nbr) which should help having to do the gymnastics Martin and Bryan brought up hth Colin Alfke Calgary Canada -Original Message- From: Lettau, Jeff The question is when I do OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not 01. Why? And how do I get these to match? The reason for this is I'm trying to do this as a i-desc But my dates don't equal because of the weird oconv issue. IF OCONV(@DATE, 'DM') - 1 < '1' THEN OCONV(@DATE, 'DY') - 1 : '12' ELSE OCONV(@DATE, 'DY') : OCONV(@DATE, 'DM') - 1; (OCONV(Inv_So_Date,'DY'):OCONV(Inv_So_Date,'DM')); IF B$Record_Type = 'BK' THEN IF @1 = @2 THEN QTY ELSE '0' ELSE '0' I'll take a better solution if someone has it since I have to do this for 36 months making 36 i-desc to put it in columns for my output that will need to be grouped and summed. Jeffrey Lettau --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] OCONV DATE ISSUE.
Why - because -1 is a mathematical operation, it is outside of the oconv so it performs the maths on the output of the oconv operation, so 02 -1 = 1. To get the required format use a mask on the whole thing. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lettau, Jeff Sent: 08 February 2008 16:35 To: u2-users@listserver.u2ug.org Subject: [U2] OCONV DATE ISSUE. The question is when I do OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not 01. Why? And how do I get these to match? The reason for this is I'm trying to do this as a i-desc But my dates don't equal because of the weird oconv issue. IF OCONV(@DATE, 'DM') - 1 < '1' THEN OCONV(@DATE, 'DY') - 1 : '12' ELSE OCONV(@DATE, 'DY') : OCONV(@DATE, 'DM') - 1; (OCONV(Inv_So_Date,'DY'):OCONV(Inv_So_Date,'DM')); IF B$Record_Type = 'BK' THEN IF @1 = @2 THEN QTY ELSE '0' ELSE '0' I'll take a better solution if someone has it since I have to do this for 36 months making 36 i-desc to put it in columns for my output that will need to be grouped and summed. Jeffrey Lettau ERP Systems Manager polkaudio Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] OCONV DATE ISSUE.
On Feb 8, 2008 9:35 AM, Lettau, Jeff <[EMAIL PROTECTED]> wrote: > The question is when I do > OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not > 01. > Why? And how do I get these to match? Because the result of the OCONV(@DATE,'DM') is a string with two characters and when you do a mathematical operation on it (-1) it converts to a number and the leading zeroes are removed. To make it two characters OCONV(OCONV(@DATE,'DM') - 1),'MR%2') I'll take a better solution if someone has it since I have to do this for 36 > months making 36 i-desc to put it in columns for my output that will need > to > be grouped and summed. Alternatively, add 0 to the original date conversion: OCONV(@DATE,'DM') + 0 ...and then you can compare strictly numerically without the leading digits. -K2 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] OCONV DATE ISSUE.
And will you have issues doing that math for January since 01-1 = 0? Caryl -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Martin Phillips Sent: Friday, February 08, 2008 9:29 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] OCONV DATE ISSUE. Hi Jeff, > The question is when I do > OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not > 01. > Why? And how do I get these to match? OCONV returns a character string result. Using it in an arithmetic expression converts it to a number. Hence 02 minus 1 is 1, not 01. There are various ways to get what you want. One would be FMT(OCONV(@DATE, 'DM')-1, 'R%2') Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB +44-(0)1604-709200 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] OCONV DATE ISSUE.
Subtracting 1 means you will get a mathematical result - leading digits are dropped. OCONV(OCONV(@DATE,'DM') - 1,'MR%2') will get you a result consistent with OCONV(@DATE,'DM') If you want to strip out leading zeros in the month regardless, add 0. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lettau, Jeff Sent: Friday, February 08, 2008 11:35 AM To: u2-users@listserver.u2ug.org Subject: [U2] OCONV DATE ISSUE. The question is when I do OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not 01. Why? And how do I get these to match? The reason for this is I'm trying to do this as a i-desc But my dates don't equal because of the weird oconv issue. IF OCONV(@DATE, 'DM') - 1 < '1' THEN OCONV(@DATE, 'DY') - 1 : '12' ELSE OCONV(@DATE, 'DY') : OCONV(@DATE, 'DM') - 1; (OCONV(Inv_So_Date,'DY'):OCONV(Inv_So_Date,'DM')); IF B$Record_Type = 'BK' THEN IF @1 = @2 THEN QTY ELSE '0' ELSE '0' I'll take a better solution if someone has it since I have to do this for 36 months making 36 i-desc to put it in columns for my output that will need to be grouped and summed. Jeffrey Lettau ERP Systems Manager polkaudio Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] OCONV DATE ISSUE.
> The question is when I do > OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, > 'DM')-1 I get 1 not 01. > Why? And how do I get these to match? In the first expression, 02 is a string. You are then applying an arithmetic operation, which coerces it into a number (1). To get them to match, either format the second number (01) or add zero to the first to coerce it (2). Brian --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] OCONV DATE ISSUE.
Hi Jeff, The question is when I do OCONV(@DATE, 'DM') I get 02 but when I do OCONV(@DATE, 'DM')-1 I get 1 not 01. Why? And how do I get these to match? OCONV returns a character string result. Using it in an arithmetic expression converts it to a number. Hence 02 minus 1 is 1, not 01. There are various ways to get what you want. One would be FMT(OCONV(@DATE, 'DM')-1, 'R%2') Martin Phillips Ladybridge Systems Ltd 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB +44-(0)1604-709200 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/