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] Convert a DateTime to Alpha for Compare

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Convert a DateTime to Alpha for Compare
 Login/Join
 
JSJohnson
Member
posted
I am pulling in a Oracle Date Time and needing to compare it to a passed in begin and end date time. I collect the begin and end date portion using the calender html tool and the Hour, Min, Second as seperate integers. I parse the begin and end date times into a alpha string and then would like to compare against my oracle datetime coming in. Of course you can't compare a datetime to an alpha and i'm pulling my hair out. I can't seem to get the begin date time strings to be converted into a focus date time field and i can't seem to get go the other way and convert the oracle datetime field into an alpha. I'd like to compare it has alpha strings. Here is the field

dPublished/HYYMD-SA=SAD_MESSAGES.SAD_MESSAGES.PUBLISHED;

How would i get the above dPublished into an Alpha field in the format HYYMD-SA so that i could compare it against the begin and end datetime strings i formated to look the same?

Thanks

John

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


WebFOCUS 7.6.8
Unix
ALL
 
Posts: 15 | Registered: March 31, 2009Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Why not convert your input dates/times to date-time fields? Something like this should work:

BEGIN_DATE/A8YY|M|D = BEGINDATE ;
BEGIN_TIME/A6       = EDIT(BEGINHOURS) || EDIT(BEGINMINUTES) || EDIT(BEGINSECONDS);
BEGIN_DTTM/HYYMDS   = HINPUT(14,BEGIN_DATE | BEGIN_TIME,8,'HYYMDS');


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Dan Satchell
Virtuoso
posted Hide Post
..... otherwise, function HCNVRT will convert date-time values to alphanumeric.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
JSJohnson
Member
posted Hide Post
Coverting the begin and end dates into date/times to compare to the published from oracle is fine with me. However it's still complaining.

BEGIN_DATE/A8YY|M|D = &BeginDate;

results in

0 ERROR AT OR NEAR LINE 21 IN PROCEDURE resend_messages_by
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
BEGIN_DATE

&BeginDate came from a post from a HTML form that used the html calender tool formating date as &DATEYYMD

Thanks

John


WebFOCUS 7.6.8
Unix
ALL
 
Posts: 15 | Registered: March 31, 2009Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Try putting single quotes around the input date:

BEGIN_DATE/A8YY|M|D = '&BeginDate';


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
JSJohnson
Member
posted Hide Post
Same Result


WebFOCUS 7.6.8
Unix
ALL
 
Posts: 15 | Registered: March 31, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
You might want to provide us an example value of &BeginDate.

This works:

-SET BeginDate = '20110425';

DEFINE FILE CAR
BEGIN_DATE/A8YY|M|D = '&BeginDate';
END

TABLE FILE CAR
PRINT
BEGIN_DATE
BY COUNTRY
END

Despite what some people believe, you do not need to know rocket science to work with date-time fields.

"Of course you can't compare a datetime to an alpha and i'm pulling my hair out" - actually, you can - here are some examples:

WHERE START_DT_CORRECTED FROM DT(20011101 00:00:00.000) TO DT(20021031 23:59:59.999);

WHERE AS_OF_TMS GE DT(&ENDDATE 00:00:00)
WHERE AS_OF_TMS LE DT(&ENDDATE 23:59:59)

WHERE SUBMITDATE FROM DT(&RNG_S.EVAL 00:00:00.000) TO DT(&RNG_E.EVAL 23:59:59.999);

The DT function converts the alpha Date/Time string to a Date/Time value that can be used in a WHERE statement in a WebFOCUS request.

You can use the Oracle function to_date to embed an alpha Date/Time string in a SQL request:
SQL SQLORA
SELECT  MAX(T1.AS_OF_TMS) FROM EDA.P7V202 T1 WHERE  
(T1.SNAPSHOT_TYPE = 'VALUATN') AND (T1.AS_OF_TMS BETWEEN  
TO_DATE('31-10-2001 00:00:00','DD-MM-YYYY HH24:MI:SS') AND  
TO_DATE('31-10-2001 23:59:59','DD-MM-YYYY HH24:MI:SS')) AND  
(T1.ACCT_ID = '000102210'); 
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Dan Satchell
Virtuoso
posted Hide Post
The direct assignment to a legacy date (A8YYMD) doesn't work because of the slashes in the input date. (Don't know why FOCUS can't figure this out, but....) One solution is to convert the input date to a SmartDate before changing it to a legacy date. This should work:

BEGINDATE/YYMD = '&BeginDate';
BEGIN_DATE/A8YY|M|D = BEGINDATE ;


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
JSJohnson
Member
posted Hide Post
quote:
BEGINDATE/YYMD = '&BeginDate';
BEGIN_DATE/A8YY|M|D = BEGINDATE ;


Awesome this worked Dan thanks so much. I still got some more changes to get the whole page working but this may have got me over the hump.


WebFOCUS 7.6.8
Unix
ALL
 
Posts: 15 | Registered: March 31, 2009Report This Post
JSJohnson
Member
posted Hide Post
To help others that may stumble across this here is the final version that worked for the begin date and time to compare against the oracle date. And for those of you who look at the code below and wonder why i couldn't just write EDIT(&BeginHour) it didn't work.

BEGINDATE/YYMD = '&BeginDate';
BEGIN_DATE/A8YY|M|D = BEGINDATE ;

BEGIN_HOURi/I2 = &BeginHour;
BEGIN_HOUR/A2 = EDIT(BEGIN_HOURi);
BEGIN_MINi/I2 = &BeginMinute;
BEGIN_MIN/A2 = EDIT(BEGIN_MINi);
BEGIN_SECi/I2 = &BeginSecond;
BEGIN_SEC/A2 = EDIT(BEGIN_SECi);
BEGIN_TIME/A6 = BEGIN_HOUR || BEGIN_MIN || BEGIN_SEC;

BEGIN_DTTM/HYYMDS = HINPUT(14,BEGIN_DATE | BEGIN_TIME,8,'HYYMDS');


WebFOCUS 7.6.8
Unix
ALL
 
Posts: 15 | Registered: March 31, 2009Report This Post
Dan Satchell
Virtuoso
posted Hide Post
If the EDIT on the input times didn't work, you might be able to combine the input data without any additional conversions:

BEGIN_TIME/A6 = '&BeginHour' || '&BeginMinute' || '&BeginSecond';


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Creating a virtual column to satisfy selection criteria is not an efficient programming method and I have never had to resort to it. Those DEFINE fields are created for every row in the Oracle table.

I would suggest you look at the code I posted - much more efficient, no field format conversions - just one alpha string conversion.

This kind of thing sometimes results in the question "why does my WebFOCUS report take so long to run?".


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
And if you really insist on creating a DEFINE field, it can be done in two lines, as in this example:

-SET &BeginDate = '2011/05/23';
-SET &BeginHour = 11;
-SET &BeginMinute = 14;
-SET &BeginSecond = 42;

 
-SET &BeginDTTM = EDIT(&BeginDate,'9999$99$99') || &BeginHour || &BeginMinute || &BeginSecond;
DEFINE FILE CAR
BEGIN_DTTM/HYYMDS = HINPUT(14, '&BeginDTTM', 8, 'HYYMDS');
END
TABLE FILE CAR
PRINT 
BEGIN_DTTM
BY COUNTRY
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 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] Convert a DateTime to Alpha for Compare

Copyright © 1996-2020 Information Builders