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     CASE OPENED: DTDIFF returns unexpected result when endDate < startDate

Read-Only Read-Only Topic
Go
Search
Notify
Tools
CASE OPENED: DTDIFF returns unexpected result when endDate < startDate
 Login/Join
 
Herbie McNerf
Member
posted
DTDIFF does not return the same values as HTDIFF when endDate < startDate. HTDIFF returns the expected values.

In the following fex file and resulting report the "Age in Weeks" should be 0 from 10/17 through 10/23, -1 from 10/24 through 10/30, -2 from 10/31 through 11/06, -3 from 11/07 through 11/13, -4 from 11/14 through 11/20, and -5 from 11/21 through 11/27.

-* AGEINDAYSDEFINE DTDIFF( TODAYTIMESTAMP, DTE, DAY )
-* AGEINWEEKSDEFINE DTDIFF( TODAYTIMESTAMP, DTE, WEEK )
TABLE FILE T1
SUM
T1.T1.AGEINDAYSDEFINE
T1.T1.AGEINWEEKSDEFINE
BY T1.T1.DTE
WHERE ( T1.T1.DTE GE DT(2015-09-01) ) AND ( T1.T1.DTE LE DT(2015-11-30) );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

Date Age in Days Age in Weeks
2015/09/01 50 7
2015/09/02 49 7
2015/09/03 48 7
2015/09/04 47 7
2015/09/05 46 6
2015/09/06 45 6
2015/09/07 44 6
2015/09/08 43 6
2015/09/09 42 6
2015/09/10 41 6
2015/09/11 40 6
2015/09/12 39 5
2015/09/13 38 5
2015/09/14 37 5
2015/09/15 36 5
2015/09/16 35 5
2015/09/17 34 5
2015/09/18 33 5
2015/09/19 32 4
2015/09/20 31 4
2015/09/21 30 4
2015/09/22 29 4
2015/09/23 28 4
2015/09/24 27 4
2015/09/25 26 4
2015/09/26 25 3
2015/09/27 24 3
2015/09/28 23 3
2015/09/29 22 3
2015/09/30 21 3
2015/10/01 20 3
2015/10/02 19 3
2015/10/03 18 2
2015/10/04 17 2
2015/10/05 16 2
2015/10/06 15 2
2015/10/07 14 2
2015/10/08 13 2
2015/10/09 12 2
2015/10/10 11 1
2015/10/11 10 1
2015/10/12 9 1
2015/10/13 8 1
2015/10/14 7 1
2015/10/15 6 1
2015/10/16 5 1
2015/10/17 4 0
2015/10/18 3 0
2015/10/19 2 0
2015/10/20 1 0
2015/10/21 0 0
2015/10/22 -1 0
2015/10/23 -2 -1
2015/10/24 -3 0
2015/10/25 -4 0
2015/10/26 -5 -1
2015/10/27 -6 -1
2015/10/28 -7 -1
2015/10/29 -8 -1
2015/10/30 -9 -2
2015/10/31 -10 -1
2015/11/01 -11 -1
2015/11/02 -12 -2
2015/11/03 -13 -2
2015/11/04 -14 -2
2015/11/05 -15 -2
2015/11/06 -16 -3
2015/11/07 -17 -2
2015/11/08 -18 -2
2015/11/09 -19 -3
2015/11/10 -20 -3
2015/11/11 -21 -3
2015/11/12 -22 -3
2015/11/13 -23 -4
2015/11/14 -24 -3
2015/11/15 -25 -3
2015/11/16 -26 -4
2015/11/17 -27 -4
2015/11/18 -28 -4
2015/11/19 -29 -4
2015/11/20 -30 -5
2015/11/21 -31 -4
2015/11/22 -32 -4
2015/11/23 -33 -5
2015/11/24 -34 -5
2015/11/25 -35 -5
2015/11/26 -36 -5
2015/11/27 -37 -6
2015/11/28 -38 -5
2015/11/29 -39 -5
2015/11/30 -40 -6

This message has been edited. Last edited by: Herbie McNerf,


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: October 21, 2015Report This Post
Francis Mariani
Expert
posted Hide Post
Welcome to FocalPoint.

make it a habit to always enclose your code within 
[code]
your code here
[/code]
tags. You can click on the </> button to generate the code tags.

Neither of the two functions you mention are WebFOCUS functions. There are DATEDIF and HDIFF, the syntax for each:

DATEDIF: Finding the Difference Between Two Dates
DATEDIF(from_date, to_date, 'component')


HDIFF: Finding the Number of Units Between Two Date-Time Values
HDIFF(end_dt, start_dt, 'component', output)


Do you see that in one the From Date is specified first and in the other it is specified second?

On FocalPoint is it also a good idea to post code that we can run - it makes it much easier for us to debug posted code. You can create re-runnable code by referencing one of the IBI-provided sample tables. Unfortunately, the readily available sample tables (e.g. CAR) are antiques that either don't have date columns or have data in date columns that are not easily usable.

Fortunately, there is an option to generate slightly more modern RDBMS tables: navigate to the appropriate WebFOCUS Reporting Server; click on the Applications tab; right-click on an application folder (e.g. ibisamp_; select New, then select Samples. A "Create Sample Procedures and Data" page is displayed, from where you can select "WebFOCUS - Retail Demo" or "WebFOCUS Star Schema with Variable Data" to create metadata and data... Check first - this might have already been done.

Meanwhile, GGORDER is a table that contains dates [2 digit year Frowner, something I don't trust]:

DEFINE FILE GGORDER
TODAYTIMESTAMP/YYMD = '1996/05/01';
ORDER_DATE_YYMD/YYMD = ORDER_DATE;

AGEINDAYS/P8 = DATEDIF(TODAYTIMESTAMP, ORDER_DATE_YYMD, 'DAY')
END

TABLE FILE GGORDER
SUM
MIN.TODAYTIMESTAMP
MIN.ORDER_DATE_YYMD
MIN.AGEINDAYS
BY ORDER_DATE_YYMD NOPRINT
END


You should be able to run this - for which dates are the results incorrect?


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
GavinL
Master
posted Hide Post
Worth the money..

http://www.amazon.com/WebFOCUS...-DATES/dp/B000NM10IG



- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server
 
Posts: 578 | Registered: October 01, 2014Report This Post
Herbie McNerf
Member
posted Hide Post
It is DTDIFF, rather than the DATEDIF, that I am using. Here is a script demonstrates the issue.

-SET &X = '2015-10-17 ' | DTDIFF(DT(2015-10-21), DT(2015-10-17), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-17), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-18 ' | DTDIFF(DT(2015-10-21), DT(2015-10-18), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-18), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-19 ' | DTDIFF(DT(2015-10-21), DT(2015-10-19), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-19), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-20 ' | DTDIFF(DT(2015-10-21), DT(2015-10-20), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-20), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-21 ' | DTDIFF(DT(2015-10-21), DT(2015-10-21), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-21), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-22 ' | DTDIFF(DT(2015-10-21), DT(2015-10-22), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-22), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-23 ' | DTDIFF(DT(2015-10-21), DT(2015-10-23), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-23), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-24 ' | DTDIFF(DT(2015-10-21), DT(2015-10-24), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-24), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-25 ' | DTDIFF(DT(2015-10-21), DT(2015-10-25), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-25), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-26 ' | DTDIFF(DT(2015-10-21), DT(2015-10-26), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-26), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-27 ' | DTDIFF(DT(2015-10-21), DT(2015-10-27), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-27), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-28 ' | DTDIFF(DT(2015-10-21), DT(2015-10-28), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-28), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-29 ' | DTDIFF(DT(2015-10-21), DT(2015-10-29), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-29), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-30 ' | DTDIFF(DT(2015-10-21), DT(2015-10-30), WEEK) | ' ' | HDIFF(DT(2015-10-21), DT(2015-10-30), 'WEEK', 'I6');
-TYPE &X
END


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: October 21, 2015Report This Post
Tom Flynn
Expert
posted Hide Post
Francis, DTDIFF is a new function in 8.1, HTDIFF is not a WF function, probably a typo...

Herbie, you need to convert your dates to smart dates for these functions to work properly with DM.
Lookup DATECVT

Also, if you want Saturday to be the beginning of the week:

SET WEEKFIRST = 6



Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Herbie McNerf
Member
posted Hide Post
Tom, I get the same results when I use DATECVT to create smart dates as when I used DT(date literal).

-SET &X = '2015-10-17 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151017, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151017), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-18 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151018, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151018), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-19 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151019, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151019), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-20 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151020, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151020), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-21 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151021, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151021), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-22 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151022, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151022), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-23 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151023, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151023), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-24 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151024, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151024), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-25 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151025, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151025), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-26 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151026, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151026), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-27 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151027, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151027), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-28 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151028, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151028), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-29 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151029, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151029), 'WEEK', 'I6');
-TYPE &X
-SET &X = '2015-10-30 ' | DTDIFF(DATECVT(20151021, 'I8YYMD', 'YYMD'), DATECVT(20151030, 'I8YYMD', 'YYMD'), WEEK) | ' ' | HDIFF(DT(20151021), DT(20151030), 'WEEK', 'I6');
-TYPE &X


Output:
2015-10-17 0 0
2015-10-18 0 0
2015-10-19 0 0
2015-10-20 0 0
2015-10-21 0 0
2015-10-22 0 0
2015-10-23 -1 0
2015-10-24 0 -1
2015-10-25 0 -1
2015-10-26 -1 -1
2015-10-27 -1 -1
2015-10-28 -1 -1
2015-10-29 -1 -1
2015-10-30 -2 -1


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: October 21, 2015Report This Post
Francis Mariani
Expert
posted Hide Post
Try DATEDIF, e.g.:
-SET &BUSDAYS_YTD = DATEDIF(DATECVT(&DATE_JAN1, 'I8YYMD', 'YYMD'),DATECVT(&PREV_BUS_DT, 'I8YYMD', 'YYMD'),'BD');


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     CASE OPENED: DTDIFF returns unexpected result when endDate < startDate

Copyright © 1996-2020 Information Builders