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     HOW: COMPUTE NUMBER_OF_DAYS/I5 = DATE_FROM_ORACLE_SQL - &DATE;

Read-Only Read-Only Topic
Go
Search
Notify
Tools
HOW: COMPUTE NUMBER_OF_DAYS/I5 = DATE_FROM_ORACLE_SQL - &DATE;
 Login/Join
 
vidas
Member
posted
Hi,

Newbie need some help with calculations:
COMPUTE NUMBER_OF_DAYS/I5 = DATE_FROM_ORACLE_SQL - &DATE;

I found that DATE_FROM_ORACLE_SQL is timestamp, noted to do calculations in SQL and so on.
I test it with DATEDIF(HDATE(TERMINAS, 'YYMD'), &DATE, 'D') and similar,
but with no results.

Thanks in advance
 
Posts: 2 | Registered: July 25, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Convert today's date to a date-time value and then use HDIFF with the SQL date-time column (DATE_FMT) and today's date as a date-time value (DT_TODAY1),

OR

Convert the SQL date-time column to a date column and then use DATEDIF with the date column (DATE_FMT2) and today's date (DT_TODAY2) as a date value:

TABLE FILE TIME
PRINT
COMPUTE DT_TODAY1/HYYMDS = HGETC(10, 'HYYMDS');
DATE_FMT
COMPUTE NUMBER_OF_DAYS1/D5 = HDIFF(DT_TODAY1, DATE_FMT, 'DAY','D5');

COMPUTE DT_TODAY2/YYMD = '&YYMD';
COMPUTE DATE_FMT2/YYMD = HDATE(DATE_FMT, 'YYMD');

COMPUTE NUMBER_OF_DAYS2/I6 = DATEDIF(DATE_FMT2, DT_TODAY2, 'D');
WHERE READLIMIT EQ 1
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
Danny-SRL
Virtuoso
posted Hide Post
Vidas,

When you created the MASTER for your Oracle table, it probably defined DATE_FROM_ORACLE_SQL with the format HYYMDs which is the time-stamp. If you do not need the time you can change the format fo your field to:
FIELD=DATE_FROM_ORACLE_SQL,FORMAT=MDYY, ACTUAL=DATE...

Then in your program you can write:
COMPUTE NUMBER_OF_DAYS/I5 = DATE_FROM_ORACLE_SQL - '&DATEMDYY';


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
jgelona
Master
posted Hide Post
To expand on DANNY-SRL's comment, if like us, where sometimes you need the time and sometimes you don't just leave both in the .mas. Something like this:

FIELD=TSTDT,ORACLE_DTTM,YYMD,DATE,$
FIELD=TSTDTTM,ORACLE_DTTM,HYYMDS,HYYMDS,$


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Carol Dobson
Guru
posted Hide Post
before you create your synonym, (if you're using the synonym wizard) you can use the setting:

ENGINE SQLORA SET DATETIME OFF

It works for SQL, should work for Oracle. That is if you don't need the time setting.

Carol



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
vidas
Member
posted Hide Post
Thanks to all for answers,
For me, newbie, most undestandable expresion at this time is

TABLE FILE SQLOUT
PRINT
.............
COMPUTE TODAY_DATE_TIME/HYYMDS = HGETC(10, 'HYYMDS'); NOPRINT
COMPUTE DIFF_DAYS/D5 = HDIFF(TODAY_DATE_TIME, DATE_TIME_FROM_ORACLE, 'DAY', 'D5');
............
 
Posts: 2 | Registered: July 25, 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     HOW: COMPUTE NUMBER_OF_DAYS/I5 = DATE_FROM_ORACLE_SQL - &DATE;

Copyright © 1996-2020 Information Builders