[SOLVED] Chage date format from MMDDYY to M/D/YYYY
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,
WebFOCUS 8105 Windows, Excel
December 05, 2017, 02:43 PM
Don Garland
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 ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 07, 2017, 09:18 AM
PO
The date format is MM/DD/YYYY
Using Don Garland's code I get 94 /24 /0004
Something might be missing.
Thanks
WebFOCUS 8105 Windows, Excel
December 07, 2017, 09:42 AM
BabakNYC
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.
WebFOCUS 8206, Unix, Windows
December 07, 2017, 02:17 PM
Doug
How about using "FORMAT=DATE"?
December 07, 2017, 03:12 PM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
December 08, 2017, 09:49 AM
Doug
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.
December 08, 2017, 11:49 AM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
December 08, 2017, 01:11 PM
BabakNYC
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.
WebFOCUS 8206, Unix, Windows
December 08, 2017, 02:03 PM
jfr99
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
WebFocus 8.201M, Windows, App Studio
December 08, 2017, 06:31 PM
Edward Wolfgram
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
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.