Hi all,

I've spent numerous hours (days?) already on trying to figure out how to convert & work with dates.
(many thanks for the answers already provided)
but is there any documentation out there explaining
- best practices on usage of date functions & conversions (age, prev year/month, 'YYYYMM' ...)
- which functions get translated to SQL (and how)

Why my question? 2 reasons:
- I've noticed the use of a single untranslatable define negates all aggregation on database level
- I've noticed the same type of functions in WF don't always translate to similar code in Oracle

Example1: Previous month on integer field YYYYMM
- SQL: TO_CHAR(ADD_MONTHS(to_date(YEAR_MONTH||'01','YYYYMMDD'), -1), 'YYYYMM')
- WF: DATECVT(DD_TIME_FIN.YEAR_MONTH | '01', 'A6YYM', 'YYMD') - 1
--> FOC2566: CANNOT BE CONVERTED TO SQL

Example2: Age calculation on datetime fields
- SQL: (SYSDATE - SUPC_DTBTH) /365
- WF: HDIFF & DATEDIF --> FOC2566: CANNOT BE CONVERTED TO SQL
(&YYMD - HDATE(SUPC_DTBTH)) --> Agg on database! (although generated TRUNC should not be necessary)

I'm getting to something working each time, but it's taking a ridiculous amount of time, so I'm hoping (praying) this type of documentation exists as I'm bound not to be the first with this issue...

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

There is a quite invaluable little booklet, called 'Almost 1001 ways to work with dates'. You may find it helpful. Last time I looked (quite a while back) it cost about 25 dollar, quite cheap for what it offers.

quote:

WF: DATECVT(DD_TIME_FIN.YEAR_MONTH | '01', 'A6YYM', 'YYMD') - 1

the format of the 1st argument isn't yym, its yymd..you've added your own '01' to it.

and &YYMD is a character string, its an &variable. If you want to work with datetime and &var in same equation, you first have to translate either the &YYMD into a datetime, or the datetime into a character string. compare like to like..

download the Using Functions manual and read the entire section on Dates and Date/Times. required reading.

if you can find a copy of the 1001 ways book Gamp refers to , super. get it.

buy a copy of WebFocus Key Sheet book. priceless!

Consider the use of "DT", as

WHERE REPORT_DATE EQ DT(2009-02-06 12:00:00AM);

@GamP: Thanks for the reference, I'll take a closer look at what's inside that booklet (does 350 pages still count as a booklet?)

@Susannah: I've come to understand I shouldn't try to do several conversions within a single define, but rather create distinct defines for each conversion. (kind of an abundance in defines, but more readable I guess)
I'll go through the functions manual in more detail as opposed to using it as a reference manual.
So far I haven't found any mention yet of which functions can be translated to SQL...

@Doug: the problem with using dates in my where-clauses is its interference with the oracle aggregation underneath (if I use monthly functions on datetimes, I'll never access the monthly aggregates)

For now, I've confined myself to a couple of standard date functions which translate to SQL and have started applying them on constants/variables rather than the database fields... Working my way through it...

From WF7703 on you can create define fields using SQL functions, just in case you cannot find a translatable function within WebFOCUS, and you want the calculation/aggregation to be done in SQL.
The syntax is quite simple, just type SQL. before the SQL function:

 SQL.TO_CHAR(ADD_MONTHS(to_date(YEAR_MONTH||'01','YYYYMMDD'), -1), 'YYYYMM')