Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Chage date format from MMDDYY to M/D/YYYY
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Chage date format from MMDDYY to M/D/YYYY
 Login/Join
 
Member
posted
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
 
Posts: 15 | Registered: November 03, 2017Reply With QuoteReport This Post
Guru
posted Hide Post
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,


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
What format (specified in the master file) is the date ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 15 | Registered: November 03, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
How about using "FORMAT=DATE"?
 
Posts: 3040 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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.
 
Posts: 3040 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
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 Smiler

/*-------------------------------------------------------------------*/
/* 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 Frowner 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,


IBI Development
 
Posts: 59 | Registered: November 15, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Chage date format from MMDDYY to M/D/YYYY

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.