I'm looking for ideas on how to format a date format from MM/DD/YY to M/D/YYYY. My customer needs it in that format.

This message has been edited. Last edited by: FP Mod Chuck,

How is the date stored; SQL, AS400 as char,int,varchar,datetime?

You can do something like this;

DEFINE FILE GGSALES
DT_PART_YR/A10=EDIT(DATE,'9999');
DT_PART_MN/A10=EDIT(DATE,'$$$$$$$99');
DT_PART_MN/A10=IF DT_PART_DY NE '10' THEN STRIP(10,DT_PART_MN,'0','A10') ELSE DT_PART_MN;
DT_PART_DY/A10=EDIT(DATE,'$$$$99');
DT_PART_DY/A10=IF DT_PART_DY NE '10' OR '20' OR '30' THEN STRIP(10,DT_PART_DY,'0','A10') ELSE DT_PART_DY;
DATE_MDYYYY/A100 = DT_PART_MN || '/' || DT_PART_DY || '/' || DT_PART_YR;
END
TABLE FILE GGSALES

BY  DATE
BY  DATE_MDYYYY
END  

This message has been edited. Last edited by: Don Garland,

What format (specified in the master file) is the date ?

The date format is MM/DD/YYYY

Using Don Garland's code I get
94 /24 /0004

Something might be missing.

Thanks

What Waz is asking is what's the format of the field in the metadata. If you open the master file you'll see a FORMAT= which will impact the answer. The code Don Garland posted works on my machine.

How about using "FORMAT=DATE"?

It's true that Don's code is working, but I can't explain how it can.

Personally, I'll go with the below code which look cleaner and doesn't used DT_PART_DY to define DT_PART_MN...

DEFINE FILE GGSALES
DT_PART_YR/A10=EDIT(DATE,'9999');
DT_PART_MN/A10=EDIT(DATE,'$$$$99');
DT_PART_DY/A10=EDIT(DATE,'$$$$$$99');
DT_PART_MN/A10=IF DT_PART_MN LT '10' THEN STRIP(10,DT_PART_MN,'0','A10') ELSE DT_PART_MN;
DT_PART_DY/A10=IF DT_PART_DY LT '10' THEN STRIP(10,DT_PART_DY,'0','A10') ELSE DT_PART_DY;
DATE_MDYYYY/A100 = DT_PART_MN || '/' || DT_PART_DY || '/' || DT_PART_YR;
END
TABLE FILE GGSALES
BY  DATE
BY  DATE_MDYYYY
END

Just another solution:

DEFINE FILE GGSALES
DT_PART_YR/A10=EDIT(DATE,'9999');
DT_PART_MN/A10=EDIT(DATE,'$$$$99');
DT_PART_DY/A10=EDIT(DATE,'$$$$$$99');
DT_PART_MN/A10=IF DT_PART_MN LT '10' THEN STRIP(10,DT_PART_MN,'0','A10') ELSE DT_PART_MN;
DT_PART_DY/A10=IF DT_PART_DY LT '10' THEN STRIP(10,DT_PART_DY,'0','A10') ELSE DT_PART_DY;
DATE_MDYYYY/A100 = DT_PART_MN || '/' || DT_PART_DY || '/' || DT_PART_YR;
END
TABLE FILE GGSALES
BY  DATE
BY  DATE_MDYYYY
END

MartinY:

quote:

but I can't explain how it can.

Don's solution is very similar to yours.

quote:

Don's solution is very similar to yours.

Doug,

It's not really the same
If you pay attention to Don's code, you'll find some errors in it.

DATE field is a FORMAT=I8YYMD so :

1- " DT_PART_DY/A10=EDIT(DATE,'$$$$99'); " should be DT_PART_MN since it's the month position not the day
2- " DT_PART_MN/A10=EDIT(DATE,'$$$$$$$99'); " should be DT_PART_DY and not have 7 $ but 6 since the I8YYMD doesn't have the slash in its format. The slash exist only on display as far as I know.

3- " DT_PART_MN/A10=IF DT_PART_DY NE '10' THEN STRIP(10,DT_PART_MN,'0','A10') ELSE DT_PART_MN; " is testing DT_PART_DY to feed DT_PART_MN.


Also when doing " NE '10' " it make the STRIP function executed when it's not needed because it STRIP for value 11, 12, 13, ...31 when not needed.
It is not bad coding but can be more efficient using the " LT '10' " instead.

If you perform Don's code as per below, you will see that it doesn't work because it reference field that don't exist. Error -3-

DEFINE FILE GGSALES
DT_PART_YR/A10=EDIT(DATE,'9999');
DT_PART_MN/A10=EDIT(DATE,'$$$$$$$99');
DT_PART_MN1/A10=IF DT_PART_DY NE '10' THEN STRIP(10,DT_PART_MN,'0','A10') ELSE DT_PART_MN;
DT_PART_DY/A10=EDIT(DATE,'$$$$99');
DT_PART_DY1/A10=IF DT_PART_DY NE '10' OR '20' OR '30' THEN STRIP(10,DT_PART_DY,'0','A10') ELSE DT_PART_DY;
DATE_MDYYYY/A100 = DT_PART_MN1 || '/' || DT_PART_DY1 || '/' || DT_PART_YR;
END
TABLE FILE GGSALES
PRINT DT_PART_YR
      DT_PART_MN
      DT_PART_DY
      DT_PART_MN1
      DT_PART_DY1
BY  DATE
BY  DATE_MDYYYY
END

If you run it as below, you will note errors -1- and -2- : that DT_PART_MN always shows 1 when it should be 01, 02, ... 12 and that DT_PART_DY is showing 01, 02, ... 12 when it should always be 01 since it's only what exist in data

DEFINE FILE GGSALES
DT_PART_YR/A10=EDIT(DATE,'9999');
DT_PART_MN/A10=EDIT(DATE,'$$$$$$$99');
DT_PART_MN1/A10=IF DT_PART_MN NE '10' THEN STRIP(10,DT_PART_MN,'0','A10') ELSE DT_PART_MN;
DT_PART_DY/A10=EDIT(DATE,'$$$$99');
DT_PART_DY1/A10=IF DT_PART_DY NE '10' OR '20' OR '30' THEN STRIP(10,DT_PART_DY,'0','A10') ELSE DT_PART_DY;
DATE_MDYYYY/A100 = DT_PART_MN1 || '/' || DT_PART_DY1 || '/' || DT_PART_YR;
END
TABLE FILE GGSALES
PRINT DT_PART_YR
      DT_PART_MN
      DT_PART_DY
      DT_PART_MN1
      DT_PART_DY1
BY  DATE
BY  DATE_MDYYYY
END

Which brings us back to the original question of what the FORMAT= for this date field. If it's a DATE-TIME field, there's an easy remedy.

DEFINE FILE GGSALES
DATE_HMDYY/HMDYY = HDTTM(DATECVT(DATE, 'I8YYMD', 'MDYY'), 8, 'HMDYY');
END
TABLE FILE GGSALES
BY DATE
BY DATE_HMDYY
BY DATE_HMDYY/HoeYY
END

Someone should really write a user function to do this, it would only be a few lines of code and would run in only a handful of instructions.... ok I'll do it

/*-------------------------------------------------------------------*/
/* xamdyy: convert a FOCUS A8YYMD to an A10 string with the format   */
/* M/D/YYYY (remove leading zeros). e.g. 20171208 -> '12/8/2017 '    */
/*-------------------------------------------------------------------*/
#include <string.h>

int amdyy(char *in,char *out)
{
char *curr=out ;

memset(out,' ',10);                     /* blank 10 byte output      */
if(*(in+4)=='0') { *curr = *(in+5); curr++; }
else { *curr=*(in+4); *(curr+1)=*(in+5); curr +=2; }
*curr++ = '/';
if(*(in+6)=='0') { *curr = *(in+7); curr++; }
else { *curr=*(in+6); *(curr+1)=*(in+7); curr+=2; }
*curr++ = '/';
memcpy(curr,in,4) ;
return(0);
}

 

Put this code into amdyy.c, and if you are under a unix platform, you can just issue:

gencpgm.sh -m cpgm amdyy
 

gencpgm.sh is an IBI provided shell script that will compile an link a c program into a user program that can be read by FOCUS.

Under unix remember to export the IBICPG environment variable for the location of the directory- e.g.:

export IBICPG=/directory/of/funcs
 

Here is a little test focexec:

 
DEFINE FILE CAR
CTR/I3 = CTR + 1;
TOD/YYMD WITH BODY = '&YYMD' ;
MYDATE/YYMD = TOD + CTR + 20 ;
ADATE/A8YYMD = MYDATE ;
MYA/A10 = AMDYY(ADATE,MYA) ;
END
TABLE FILE CAR
PRINT ADATE MYA
IF RECORDLIMIT EQ 30
END
-RUN
-EXIT
 

With the result:

 
 ADATE       MYA                                                               
 -----       ---                                                               
 2017/12/29  12/29/2017
 2017/12/30  12/30/2017
 2017/12/31  12/31/2017
 2018/01/01  1/1/2018  
 2018/01/02  1/2/2018  
 2018/01/03  1/3/2018  
 2018/01/04  1/4/2018  
 2018/01/05  1/5/2018  
 2018/01/06  1/6/2018  
 2018/01/07  1/7/2018  
 2018/01/08  1/8/2018  
 2018/01/09  1/9/2018  
 2018/01/10  1/10/2018 
 2018/01/11  1/11/2018 
 2018/01/12  1/12/2018 
 2018/01/13  1/13/2018 
 2018/01/14  1/14/2018 
 2018/01/15  1/15/2018 

 

Unfortunately, I can't give instructions about how to do this for all the different platforms we support so you must find and read the documentation for your platform. The amdyy.c program itself, though, is very simple (and fast), as you can see.

Good luck,