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] DB2 Date/Timestamp parameter

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] DB2 Date/Timestamp parameter
 Login/Join
 
MO Admin
Gold member
posted
I am trying to create several reports based on a DB2 database that all run with date ranges. Several of the reports have parameters based on datetimestamp fields (i.e. Where DTS GE Begin_Date and DTS LE End_Date). However, when running for a month, I have to input the first day of the reporting month to the first day of the next month, when what I really want to put in is the first day of the reporting month to the last day of the reporting month. I assumed the LE command would take care of that, but when I use the first to last days, it does not pick up date from the last day of the month.

When I use the same code against just a date field, it works correctly. Can someone explain why it is doing this and maybe what I can do to remedy it?

Here is the generic where clause I use:
WHERE ( DATE_TIME GE DT(&START_DATE) ) AND ( DATE_TIME LE DT(&END_DATE) );

This message has been edited. Last edited by: MO Admin,


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
Darin Lee
Virtuoso
posted Hide Post
Are you using the full date-time stamp or just the date portion? My guess is that the LE is checking against the time of 00:00:00.00000 which would be the first millisecond of the last day (and thus eliminating any further timstamps for the last day) when it actually should be checking against 23:59:59.99999 - the last one of the last day. Otherwise, check to make sure that your comparision is only against the date-portion of the timestamp. Not sure how that is translated to SQL for DB2.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Darin beat me to the post, but...

If you are using the DT function without supplying the time portion, then it is defaulting to 00:00:00. If your DATE-TIME column has the value 20100430 00:00:01 your WHERE clause will not find that record because the WHERE clause becomes WHERE 20100430.00:00:01 LE 20100430.00:00:00, which fails. To avoid this problem, you could set the time to midnight:

WHERE ( DATE_TIME GE DT(&START_DATE) ) AND ( DATE_TIME LE DT(&END_DATE 23:59:59) );


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Darin Lee
Virtuoso
posted Hide Post
However, I often find it easier to just use LT the first day of the next month@midnight because I can always calculate the value as month/01/2010 whereas the last day of the month will be 28, 29, 30, or 31, depending on the month and often needs to be an additional date calculation.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
MO Admin
Gold member
posted Hide Post
Thank you for the quick responses. I added the midnight timestamp to the back of my end date parm in the where clause and that worked. We prefer to use the last day of the month instead of the first day. We always manually put it in, so the difference in number of days for each month is not an issue for us.

Thanks again.


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report 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] DB2 Date/Timestamp parameter

Copyright © 1996-2020 Information Builders