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     HOW: COMPUTE NUMBER_OF_DAYS/I5 = DATE_FROM_ORACLE_SQL - &DATE;
Go
New
Search
Notify
Tools
Reply
  
HOW: COMPUTE NUMBER_OF_DAYS/I5 = DATE_FROM_ORACLE_SQL - &DATE;
 Login/Join
 
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, 2006Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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: 1950 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
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: 950 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.