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] Simple Date - strange result trying to load

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Simple Date - strange result trying to load
 Login/Join
 
Rob Van Brunt
Member
posted
Input file has date as 02202006 named START_MDYY. I attempt to reformat it to 2006-02-20 using DEFINE to prepare it for bcp load into a sql server table.

DEFINE FILE CECACCPR

YYYY /A4 = EDIT(START_MDYY,'$$$$9999');
MM /A2 = EDIT(START_MDYY,'99$$$$$$');
DD /A2 = EDIT(START_MDYY,'$$99$$$$');
START_MDYY_A10 /A10 = YYYY | '-' | MM | '-' | DD;

The resulting value in the bcp file is 8402-00-03. NOT GOOD. I’m new and appreciate any advice you can offer as to what is happening and how to correct it. Thanks, rvb

This message has been edited. Last edited by: Kerry,


WF 7.1.7 on Win Server 2003 migrating to WF 7.7.04 on Win Server 2008
HTML, RTF, Excel and PDF
 
Posts: 15 | Registered: August 31, 2009Report This Post
rfbowley
Platinum Member
posted Hide Post
It looks like you are trying to use EDIT on a Date formated field. That will produce strange results.

Try the DATECVT function, i.e.:

 
DEFINE FILE CAR
  START_MDYY/MDYY WITH COUNTRY = &MDYY;
  START_DATE_YYMD/YYMD = DATECVT(START_MDYY, 'MDYY', 'YYMD');
  START_DATE_2/A10 = DATECVT(START_DATE_YYMD, 'YYMD', 'A8YYMD');
END

TABLE FILE CAR
PRINT
  START_MDYY
  START_DATE_YYMD
  START_DATE_2
WHERE READLIMIT EQ 1
END


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Rob Van Brunt
Member
posted Hide Post
Thanks for the reply and the nice example. When I try it my TABLE FILE fails because it does not recognize START_DATE_2? There must be other issues that I'm not seeing. Will your code example put a - between the date values? Like this YYYY-MM-DD.


WF 7.1.7 on Win Server 2003 migrating to WF 7.7.04 on Win Server 2008
HTML, RTF, Excel and PDF
 
Posts: 15 | Registered: August 31, 2009Report This Post
Waz
Expert
posted Hide Post
What format is START_MDYY ?


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
Venkat-
Silver Member
posted Hide Post
Hi Rob,

I added an extra line to robert's previous code. Here is the example:

DEFINE FILE CAR
START_MDYY/MDYY WITH COUNTRY = &MDYY;
START_DATE_YYMD/YYMD = DATECVT(START_MDYY, 'MDYY', 'YYMD');
START_DATE_2/A10 = DATECVT(START_DATE_YYMD, 'YYMD', 'A8YYMD');
START_DATE_REDEFINE/A10 = EDIT(START_DATE_2, '9999-99-99' );
END

TABLE FILE CAR
PRINT
START_MDYY
START_DATE_YYMD
START_DATE_2
START_DATE_REDEFINE
WHERE READLIMIT EQ 1
END

Hope this helps!

Regards,
Venkat


product release:8203
o/s: windows 10
expected o/p formats: HTML,EXCEL,PDF
 
Posts: 34 | Location: Kearney, Nebraska | Registered: December 14, 2011Report This Post
Rob Van Brunt
Member
posted Hide Post
in the .mas it is MDYY

FIELD=START_MDYY ,START_MDYY
,MDYY ,A8
,MISSING=ON
,$

I'm considering changing MDYY to A8 to see if the EDIT returns what I need. At this point I don't see why I can't treat the input file value of 02202006 as A8 so I can use EDIT. Please advise if I'm looking for trouble. Thank you Waz for replying.


WF 7.1.7 on Win Server 2003 migrating to WF 7.7.04 on Win Server 2008
HTML, RTF, Excel and PDF
 
Posts: 15 | Registered: August 31, 2009Report This Post
Rob Van Brunt
Member
posted Hide Post
Thank you Venkat, for showing me how to get the - into the field using another EDIT. I will try it.


WF 7.1.7 on Win Server 2003 migrating to WF 7.7.04 on Win Server 2008
HTML, RTF, Excel and PDF
 
Posts: 15 | Registered: August 31, 2009Report This Post
Waz
Expert
posted Hide Post
Is this the report output ?

Can you just use 
TABLE FILE CAR
PRINT COUNTRY
COMPUTE START_MDYY/MDYY = &MDYY ;
COMPUTE START_YYMD/YY-M-D = START_MDYY ;
END


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
Rob Van Brunt
Member
posted Hide Post
I changed the .mas to define START_MDYY as A8 instead of MDYY. This allowed the my original EDIT logic to work as expected (MAJOR thank you to rfbowley for the clue that: EDIT on a Date formated field will produce strange results). That really got me thinking that there was no logical reason that this field needs to be defined as MDYY in my case.

Waz, thanks for the example using compute with YY-M-D. Thank you (all responders) for helping to open my mind to WF coding techinques that I'm lacking and now learning thanks to YOU. Sincerly, rvb


WF 7.1.7 on Win Server 2003 migrating to WF 7.7.04 on Win Server 2008
HTML, RTF, Excel and PDF
 
Posts: 15 | Registered: August 31, 2009Report This Post
Waz
Expert
posted Hide Post
Thanks for giving something back, as we spend our own time answering and suggesting solutions.

Makes me feel like it is worthwhile posting on the forum.

Good One


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
  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] Simple Date - strange result trying to load

Copyright © 1996-2020 Information Builders