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] UGHHHHH!!!!!! I HATE WEBFOCUS DATES
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] UGHHHHH!!!!!! I HATE WEBFOCUS DATES
 Login/Join
 
Silver Member
posted
I have never worked with a language that makes formatting a date so ridiculously difficult. I usually end up spending less time on an entire report than I do just formatting a date.

After spending literally all morning trying to convert a date, I finally ended up doing a define in the masterfile and using a DB_EXPR with a to_char against the date in the database. Took me all of 10 seconds.

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


8.0.0.8
Linux/windows
 
Posts: 36 | Registered: October 24, 2012Report This Post
Master
posted Hide Post
Well, I don't want anyone to get hurt... but I understand where you are coming from. Dates are pretty perplexing in WebFOCUS.

What exactly were you trying to do? I've suffered with WebFOCUS dates for a while now and I can probably get you an example of something you are looking for.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
<Emily McAllister>
posted
Kris,

I'm sorry you had a frustrating experience with dates. Have you taken a look at our documentation on date formatting?
For a starter you can check out our Using Functions guide.

If you can provide more detail on the exact problem you were having, we can help you figure out what to do.

Emily McAllister
Focal Point Moderator
 
Report This Post
Platinum Member
posted Hide Post
OK... and now what's your question to this forum?

Not sure what you are trying to accomplish here with a subject title like this.

You could also say the endless possibilities WebFOCUS has with dates makes it more advanced..


WebFOCUS 8105m
Windows 7, All Outputs

Member of the Benelux Usergroup
 
Posts: 198 | Location: Amsterdam | Registered: August 24, 2011Report This Post
Expert
posted Hide Post
I've been in the game for about 20 years and I have to say that date manipulation in WebFOCUS can be quite frustrating. Have you taken a good look at the syntax for the function DATETRAN? Why is this the only function that requires quotes AND brackets for some (but not all) of the parameters? I could probably pick apart a few other date/date-time functions.

Kris, let us know of your requirement - I'm sure we can help.


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
Platinum Member
posted Hide Post
Hi

In WebFOCUS 8105 you have new simplified date time functions. additional new simplified functions can be also found in 8201

reporting language -> using functions
There are several new sections for the simplified functions

http://webfocusinfocenter.info...ng/tech-library.html


Yours,
Eran
SRL Products

http://www.srl.co.il

 
Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 2005Report This Post
Master
posted Hide Post
quote:
Originally posted by WF_IL:
In WebFOCUS 8105 you have new simplified date time functions.

I did not know that. Thanks for the link!


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Expert
posted Hide Post
SO, someone realized that it IS frustrating to use date/date-time functions and created new ones. I certainly hope that these new functions COMPLETELY replace the old ones and can be used in WebFOCUS AND Dialogue Manager code. Otherwise we have a bigger mess.


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
Virtuoso
posted Hide Post
I must be on the outside looking in on this one. In my 20+ years of Focus/WebFOCUS, I've never had any trouble with dates and date functions. I guess I've just been lucky.

That being said, these days I do lean to the DB_EXPR function for 90% of my date work. I do this almost entirely because of performance and throughput.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Master
posted Hide Post
quote:
Originally posted by dhagen:
In my 20+ years of Focus/WebFOCUS, I've never had any trouble with dates and date functions.

I have a 200+ page book dedicated EXCLUSIVELY to WebFOCUS date usage, and I still struggle. For me, the syntax is strange and it is impossible for me to memorize it.

Then there are several date types that usually end up having to be converted to other date types. And date functions embedded in other date functions. It gets very confusing.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Expert
posted Hide Post
quote:
I must be on the outside looking in on this one. In my 20+ years of Focus/WebFOCUS, I've never had any trouble with dates and date functions.



I'm with you.

As long as you understand what the format is and if its DM or not, no problems


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
It takes years of experience to be comfortable with man-handling dates. dhagen + Waz + Francis = ~ 60 years.


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
Expert
posted Hide Post
I think one of the basic causes of this lack of understanding of WF dates, is that most people are familiar with SQL dates.

WebFOCUS need to support SQL (and its different flavours) and the 300+ other data sources.

IF a date is stored/read into or converted into a smartdate, then all is very simple.

Datetime are a little bit more interesting to play with.

But to all out there, RTFM (Read The Functions Manual), do a search of the forum and get a copy of the "(Almost) 1001 Ways to Work With Dates in WebFOCUS" book.


"Give a man a fish, feed him for a day....."


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
quote:
As long as you understand what the format is and if its DM or not, no problems

Totally agree with this Waz and also with Francis, experience is often the key.

The main misunderstanding I see is that there are many inexperienced folks who think that 20161115 is a date. They get disheartened when you tell them that it isn't a date but an integer value which we humans interpret as a date!

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
I think it's fair to say that almost all web-focus "functions" are ****.
quote:

STRREP (inlength, instring, searchlength, searchstring, replength, repstring, outlength, output)


quote:

TRIMV(trim_where, source_string, upper_limit, pattern, pattern_limit, output)


really?


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Master
posted Hide Post
quote:
Originally posted by Waz:
But to all out there, RTFM (Read The Functions Manual), do a search of the forum and get a copy of the "(Almost) 1001 Ways to Work With Dates in WebFOCUS" book.

I own the "(Almost) 1001 Ways to Work With Dates in The Tower of Babel." It was a fine effort at making sense of WebFOCUS dates, but it's a little like putting lipstick on a pig.

It's good news, however, that date simplification is starting to happen in the latest releases.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Master
posted Hide Post
quote:
I own the "(Almost) 1001 Ways to Work With Dates in The Tower of Babel."


I also own the book and it has an overwhelming amount of information, so you never find what your looking for. Luckily I purchased the Version 7 release for only $19.95 and saved on the $100+ for the version 8 one.



- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server
 
Posts: 578 | Registered: October 01, 2014Report This Post
Silver Member
posted Hide Post
Thanks everybody for your replies. I have a date in the database. The date in the masterfile is pulled in the following format YYMD. I need this date in an additional format of Y-Mt. I have tried CHGDAT, EDIT, DATECVT, DATETRAN, DPART, and who remembers what else. I tried pulling the date apart in the masterfile and then concatenating the pieces together in the fex. That didn't quite work either. I could see the year (16) and the month (Nov), but when I tried putting them together I got something on the order of '16-340' (some kind of numeric value rather than the abbreviated month.) I have done searches on this forum, but nothing really looked all that promising. (Btw... why do people automatically assume that if you write something in this forum that you have not done any previous research - ie., looked at the forum or read the documentation?) I have read each of the entries in the documentation about date formatting and editing, and I DO have the book (Almost) 1001 Ways to Work With Dates in WebFOCUS. (BTW, loved your description of this Squatch.) I'm sorry that I am so inept at trying to figure this out, but this post is mostly about my total frustration with the craziness of how WebFocus handles dates and my inability to once and for all figure out when and how to use these different functions. Having to go through 3 or 4 conversions of a date to finally get to the format you need/want seems like a lot of work for such little return. I guess it is too bad that I don't have 8.1.0.8 which has some simpler formatting techniques.

I still am trying to get this to work, the DB_EXPR is giving me some kind of error message, so it is back to the drawing board and another day of trying to figure out how to do something that should be relatively simple. Believe me when I say that figuring out how to format dates in this language takes up the majority of my time when trying to format a report. If I didn't need to deal with dates, I could whip out a report in no time. A lot of times when I run into this problem, I dump the masterfile and pull everything in a sql passthru. However, I am just enhancing an already existing report rather than writing it from scratch.

Thanks for listening. And if you find that magical conversion combination between 'yyymd' and 'Y-Mt' pass it along.

Thanks again.


8.0.0.8
Linux/windows
 
Posts: 36 | Registered: October 24, 2012Report This Post
Silver Member
posted Hide Post
Oh and btw... I really appreciate the fact that I'm not the only one frustrated with dates. Thanks again guys for your responses - it helps to know that other people have had problems with dates. Totally am amazed at the knowledge and quality of people on this forum.


8.0.0.8
Linux/windows
 
Posts: 36 | Registered: October 24, 2012Report This Post
Virtuoso
posted Hide Post
Kris,
Your problem is the dash in 'Y-Mt'.
Without it, it is simple:
Say your date is KDATE/YYMD.
Simply
  
DEFINE FILE fff
MDATE/YMT=KDATE;
END

However, you want to insert a '-' and do away with the space. Here is a possiblility:
DEFINE FILE CAR
KDATE/A6YMD='&YMD';
MDATE/A4YM=EDIT(KDATE,'9999');
QDATE/A12=EDIT(FPRINT(MDATE, 'A4YMT', 'A12'),'99-$999');
END
TABLE FILE CAR
PRINT COUNTRY MDATE QDATE 
END


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
Master
posted Hide Post
If I understand your requirements correctly: Your incoming data is an Oracle DATE-TIME, and you need to reformat the data into YY-Mt.

Can you try to redefine your incoming Oracle date to a DATE-TIME field with the format that meets your requirements?

This message has been edited. Last edited by: David Briars,
 
Posts: 822 | Registered: April 23, 2003Report This Post
Silver Member
posted Hide Post
David, am I suppose to put that in my define? I tried

ENRLMTHYR/HYYMt- = MBR.ENRL_DT ;
and also
ENRLMTHYR/HYMt- = MBR.ENRL_DT ;

Both of these I get this error

0 ERROR AT OR NEAR LINE 98 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
ENRLMTHYR



I think I need to retire. LOL


8.0.0.8
Linux/windows
 
Posts: 36 | Registered: October 24, 2012Report This Post
Master
posted Hide Post
One way you would get the error message is if ENRL_DT was not a DATE-TIME field.
 
Posts: 822 | Registered: April 23, 2003Report This Post
Expert
posted Hide Post
Kris, you mentioned that the date column has format YYMD in the master. Here is an example using the dreaded DATETRAN function:

DEFINE FILE EMPLOYEE
ENRL_DT_YYMT/A20 = DATETRAN (HIRE_DATE, '(YM)', '(-t)', 'EN', 20, 'A20')
END

TABLE FILE EMPLOYEE
PRINT
ENRL_DT_YYMT
END
(HIRE_DATE would be your YYMD column)


Can one of the smarty-pants tell me why this seems to work? Eeker

Syntax for DATETRAN:
DATETRAN (indate, '(intype)', '([formatops])', 'lang', outlen, output)


That "intype" really isn't an 'input type' - it looks like it's used to specify which date components you want in the output. "-t" seems to provide Y-Mt.

Does the 1001 Spells for Sorcerers book have examples for DATETRAN?


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
Expert
posted Hide Post
Hi Kris,

What is the ACTUAL format of the date in the master file ?

And the underlying columns format ?

Is it DATE and datetime, if so, you may be able to change the format to HYYMD- and HYYMDs. This is all guess work, but its a possibility.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
quote:
Originally posted by Francis Mariani:
...Does the 1001 Spells for Sorcerers book have examples for DATETRAN?

Not that I can see. The Third Edition 2011 Spell book has an incantation for it starting near the bottom of page 23, which just defines it. The chanting spills over to the top of page 25.

Here is the potion:

field2/Afmt = DATETRAN(indate, '(intype), '(outtype)', 'language', 'outlength');

(It seems to not only be missing a closing apostrophe for intype, but the complete outformat as well. The Spell fails!)

The intype appears to describe the input SmartDate fieldname, but instead seems to be related to the output as you have noted. The outtype is defined as "specify options for leading zeroes, month and delimiters."

Instead of intype and outtype, it probably should say outtype and format options.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Silver Member
posted Hide Post
Oh, thank you, thank you, thank you. I used what you sent Francis and it worked like a charm. Thank you soooooo much!!!!!


8.0.0.8
Linux/windows
 
Posts: 36 | Registered: October 24, 2012Report This Post
Expert
posted Hide Post
Glad to help Smiler

I find the DATETRAN function syntax nonsensical but powerful. The only way I can use it is to start with an existing example and then fiddle with it.


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
Master
posted Hide Post
quote:
Originally posted by Dave:
I think it's fair to say that almost all web-focus "functions" are ****.
quote:

STRREP (inlength, instring, searchlength, searchstring, replength, repstring, outlength, output)


quote:

TRIMV(trim_where, source_string, upper_limit, pattern, pattern_limit, output)


really?

Exactly. I do not understand why I have to input what seems like dozens of unnecessary input parameters to get a result.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Master
posted Hide Post
quote:
Originally posted by GavinL:
quote:
I own the "(Almost) 1001 Ways to Work With Dates in The Tower of Babel."


I also own the book and it has an overwhelming amount of information, so you never find what your looking for. Luckily I purchased the Version 7 release for only $19.95 and saved on the $100+ for the version 8 one.

I could have written your words verbatim to express my feelings. That is EXACTLY how I feel. It is easy to find the right chapter for what you want to do, but it gets kind of foggy and hazy after that.

Unfortunately, I paid full price.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] UGHHHHH!!!!!! I HATE WEBFOCUS DATES

Copyright © 1996-2020 Information Builders