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,

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.

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

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..

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.

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

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!

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.

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.

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.

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

It takes years of experience to be comfortable with man-handling dates. dhagen + Waz + Francis = ~ 60 years.

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....."

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

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?

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.

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.

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.

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.

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

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,

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

One way you would get the error message is if ENRL_DT was not a DATE-TIME field.

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?

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?

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.

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.

Oh, thank you, thank you, thank you. I used what you sent Francis and it worked like a charm. Thank you soooooo much!!!!!

Glad to help

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.

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.

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.