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] Date Conversion

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Date Conversion
 Login/Join
 
WF Learner
Silver Member
posted
Hi,
In below code i am getting a user date input via calendar in July 14, 2020 format which i am trying to convert to mm/dd/yyyy format because my field in the table is in this format. I could get results till YYMD but after that I am unable to do so. Below is the code snippet for your reference.
-DEFAULTH &DATE_C='';
-DEFAULTH &DATE_5='';

DEFINE FILE CAR
DATE_BEGIN/A17='&DATE_START.(|FORMAT=YYMD).From Date.';
DATE_C/YYMD=HINPUT(17,DATE_BEGIN,20,'HYYMD');
DATE_5/YYMD=DATEADD(DATE_C,'D',5);
END

-TYPE &DATE_START
-TYPE &DATE_C
-TYPE &DATE_5

I selected calendar date as July 12, 2020. view source shows output correctly as
-TYPE &DATE_START [A12]=July 12, 2020
-TYPE &DATE_C [N08]=20200712
-TYPE &DATE_5 [N08]=20200715

Till this i am fine. Now i need to convert this to the format MM/DD/YYYY [A10] so i can fetch rows having dates between 07/12/2020 and 07/15/2020 writing condition in the where clause on Table.
I am new to WF and sitting on this trying whole day. Help is much appreciated.

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 35 | Registered: July 17, 2020Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
Kokila

In the html composer when you select the calendar control in the Settings tab there is a 'Date format in Data Source' drop down that you can set to MM/DD/YYYY. Have you tried that?


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
MartinY
Virtuoso
posted Hide Post
DATE_5/YYMD=DATEADD(DATE_C,'D',5);
DATE_5A/A8MDYY=DATE_5;


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
WF Learner
Silver Member
posted Hide Post
Hi Chuck, I only see fex code editor and not any other Infoassist option to change the settings of date format.
 
Posts: 35 | Registered: July 17, 2020Report This Post
WF Learner
Silver Member
posted Hide Post
Hi MartinY,
I see blank value and not desired date format after adding your code. Please advise if i am doing anything wrong.
-DEFAULTH &DATE_C='';
-DEFAULTH &DATE_5='';
-DEFAULTH &DATE_5A='';
DEFINE FILE CAR
DATE_BEGIN/A17='&DATE_START.(|FORMAT=YYMD).From Date.';
DATE_C/YYMD=HINPUT(17,DATE_BEGIN,20,'HYYMD');
DATE_5/YYMD=DATEADD(DATE_C,'D',5);
DATE_5A/A8MDYY=DATE_5;
END

-TYPE &DATE_START
-TYPE &DATE_C
-TYPE &DATE_5
-TYPE &DATE_5A
I selected calendar date as July 12, 2020. view source shows output correctly as
-TYPE &DATE_START [A12]=July 12, 2020
-TYPE &DATE_C [N08]=20200712
-TYPE &DATE_5 [N08]=20200715
-TYPE &DATE_5A [N01] =
 
Posts: 35 | Registered: July 17, 2020Report This Post
MartinY
Virtuoso
posted Hide Post
Assuming that the sample code that you provide is the whole code, I don't understand how you can have value in &DATE_C, &DATE_5 since they are only defaulted and have no value assigned

Below code create field (not parameter) with proper value
DEFINE FILE CAR
DATE_BEGIN/A17='&DATE_START.(|FORMAT=YYMD).From Date.';
DATE_C/YYMD=HINPUT(17,DATE_BEGIN,20,'HYYMD');
DATE_5/YYMD=DATEADD(DATE_C,'D',5);
DATE_5A/A8MDYY=DATE_5;
END
TABLE FILE CAR
PRINT DATE_BEGIN
      DATE_C
      DATE_5
      DATE_5A
BY COUNTRY NOPRINT
WHERE READLIMIT EQ 1;
END

Below assign field value to parameters
-DEFAULTH &DATE_C='';
-DEFAULTH &DATE_5='';
-DEFAULTH &DATE_5A='';
DEFINE FILE CAR
DATE_BEGIN/A17='&DATE_START.(|FORMAT=YYMD).From Date.';
DATE_C/YYMD=HINPUT(17,DATE_BEGIN,20,'HYYMD');
DATE_5/YYMD=DATEADD(DATE_C,'D',5);
DATE_5A/A8MDYY=DATE_5;
END
TABLE FILE CAR
PRINT DATE_BEGIN
      DATE_C
      DATE_5
      DATE_5A
BY COUNTRY NOPRINT
WHERE READLIMIT EQ 1;
ON TABLE HOLD AS TMP
END
-RUN
-READFILE TMP
-TYPE DATE_START: &DATE_START
-TYPE DATE_C: &DATE_C
-TYPE DATE_5: &DATE_5
-TYPE DATE_5A: &DATE_5A

When posting code or data sample, please use to code tag
Last icon on the ribbon that looks like below
</>


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
WF Learner
Silver Member
posted Hide Post
Thank you very much MartinY for all your help. First code snippet works fine for my requirement. But when i have to pass the date on to a condition on where clause of the table query that is when i get hit. I get below error.
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
Please advise.
 
Posts: 35 | Registered: July 17, 2020Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
Kokila

What is the format of the field in the database referenced in your WHERE clause? Please post your code as well..


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
WF Learner
Silver Member
posted Hide Post
Hi MartinY,
Thank you for your inputs. The code worked.
Hi Chuck,
Thank you for getting back.
I though have a question. Can we not suppress printing compute column in a table?
 
Posts: 35 | Registered: July 17, 2020Report This Post
WF Learner
Silver Member
posted Hide Post
I added the calculation in DEFINE and it worked as expected. Thank you both.
 
Posts: 35 | Registered: July 17, 2020Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
Kokila

You can suppress a COMPUTE, while in InfoAssist right mouse click on it and choose Visibility / Hide


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 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] Date Conversion

Copyright © 1996-2020 Information Builders