WebFOCUS functions in general, but dates in particular, are needlessly difficult to work with. You get used to them, but that does not excuse the state they're in. The simplified functions are a big improvement, but they're incomplete at the moment.

I never understood why 'smart dates' are called smart. They're anything but smart. Calls that are simple in SQL require nested function calls or multiple statements in WF, and then you still often don't get quite what you wanted. You compromise.

And some things with dates are nearly impossible to achieve in WF. Try converting a date to a week-number and a year separately and correctly(!) for all days in weeks 1 or 53 - you'll end up with the wrong year for several days in those weeks, because there is no way to specify that you want the year that the week is part of. You'll get the year that the date is in.

In SQL, you can get those with, for example: select to_char(date, 'IW') as week, to_date(date, 'IYYY') as year from ... and you'll get the correct values.

Or try getting a date in a specific output format for which WF didn't pre-define one. There are examples of that in these forums.

Having to deal with time-zone transformations in date-time values is another fun challenge. I don't think that's even possible.

Need to handle a date or a date-time in dialog manager? Good luck with that! Especially smart dates are fun, because in many cases they will work just fine, but then you get a smart date value where a byte in the date is a 0 and your fex aborts...

There is plenty of room for improvement here. For date-times, I think the standard SQL date handling functions would be a good starting point for an API. Those are smart dates that deserve that name.

SQL, SQL, SQL

quote:

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

I'll throw 18 more on top of that. I can do dates, but they're a hassle. I always have to look something up.

Following up to my previous post, were I was thinking that the input was an incoming date-time column from a relational table..

Here is an example of reformatting a date-time, as well as, using one of the WebFOCUS date-time functions, running against one of the MS SQL Server Adventureworks sample tables..

MFD definition of the date-time column:

FIELDNAME=RATECHANGEDATE, ALIAS=RateChangeDate, USAGE=HYYMDs, ACTUAL=HYYMDs, $  

DEFINE FILE EMPLOYEEPAYHISTORY
-* Reformat date-time to YY-Mt date-time.
 RATECHANGE_YYMT/HYYMt- = RATECHANGEDATE;
-* Get the day of the week 'part' of the date-time.  
 DAY_OF_WEEK/I2 = HPART(RATECHANGEDATE, 'DW', 'I2');
END
-*
TABLE FILE EMPLOYEEPAYHISTORY
 PRINT RATECHANGEDATE
       RATECHANGE_YYMT 
       DAY_OF_WEEK
 IF READLIMIT EQ 2
END  

  RATECHANGEDATE           RATECHANGE_YYMT  DAY_OF_WEEK                         
  --------------           ---------------  -----------                         
  2009/01/14 00:00:00.000  2009-Jan                   4
  2008/01/31 00:00:00.000  2008-Jan                   5 

I don't know that I have ever experienced any of the issues Wep mentions. Time zones are not an issue for me because everything is in the same time zone. As for smart dates in Dialogue Manager, they are not a valid format. All variables in Dialogue Manager are strings. Also, if I have a field in one of our Oracle tables that is a true date/time field, I will generally have 2 entries in the master, something like this:

FIELD=REFER_DT,ALIAS=REFERRAL_DATETIME,USAGE=YYMD,ACTUAL=DATE,$
FIELD=REFER_DTTM,ALIAS=REFERRAL_DATETIME,USAGE=HYYMDS,ACTUAL=HYYMDS,$

Now I can use date functions on REFER_DT and date/time functions on REFER_DTTM.

quote:

...Now I can use date functions on REFER_DT and date/time functions on REFER_DTTM.

+1

I don't know why IBI hasn't come up with some better solution to deal with time zones... or to even just add a timezone data type to start with. I've had a NFR in for it for I think at least a year now... probably needs another 9 years before they add support for timezones into the product.

Seems to me that time zone support would depend on how date/time fields are stored internally (in the database, focus database and flat files). I don't know that I have ever seen any documentation on that. For example, I know that some date/time field like the next run date/time are stored in Zulu format in the Report Caster Repository. Knowing that, you can always adjust, for example we are Zulu time + 6 hours.

The other issue would be day light savings time and who switches and who doesn't. I know when I lived in Louisville, Kentucky switched but Indiana didn't except for the southern counties along the Ohio River across the river from Louisville did switch.

Indiana uses Central Time in the northwest and southwest corners of the state, but Eastern Time everywhere else.

Imagine what it's like to work in one time zone and go home to another!

Jgelona,

the main problem is that WF doesn't even have a data type for timezone. It all starts from there. All the major databases do though. But you can't take advantage of any of that because WF has no idea what the timezone of the data element is.