Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2017Report 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, 2005Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2017Report 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: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Expert
posted Hide Post
How about using "FORMAT=DATE"?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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: 1853 | Location: New York City | Registered: December 30, 2015Report 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: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report 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: 61 | Registered: November 15, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders