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] Date Conversion
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Date Conversion
 Login/Join
 
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: 31 | Registered: July 17, 2020Reply With QuoteReport This Post
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: 2046 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
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: 2388 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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: 31 | Registered: July 17, 2020Reply With QuoteReport This Post
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: 31 | Registered: July 17, 2020Reply With QuoteReport This Post
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: 2388 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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: 31 | Registered: July 17, 2020Reply With QuoteReport This Post
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: 2046 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
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: 31 | Registered: July 17, 2020Reply With QuoteReport This Post
Silver Member
posted Hide Post
I added the calculation in DEFINE and it worked as expected. Thank you both.
 
Posts: 31 | Registered: July 17, 2020Reply With QuoteReport This Post
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: 2046 | Location: Customer Support | Registered: April 12, 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] Date Conversion

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