We have a new installation (and by "new", I mean ISP, hardware, OS, database, and, of course, WebFocus) of WebFocus 5.3. This application was previously running on a Windows 2000/SQL 2000/WebFocus 5.2 installation. When we moved to the new platform, we started having all kinds of trouble with dates. The problem appears to be that all the dates being pulled from SQL Server 2005 are coming across in the format yyyy-mm-dd rather than yyyymmdd.

Unfortunately, WebFocus seems to be expecting yyyymmdd for all our dates that are (and have been) defined as being in the YYMD format. It does not translate properly.

We've worked around a couple of the problems by changing the YYMD to A10 (and then, if necessary, creating a virtual field that strips out the hyphens before converting back to YYMD ... which also shows signs of not being totally successful, because of the behavior in more complex fex files.) However, dealing with this on a one-off basis will be very time consuming, particularly if this turns out to be a systemic problem.

If anyone has any ideas on this situation (as I'm now on my second week), I would be delighted to hear about it.

Thanks,
Jonathan

Jon

Webfocus should carry out auto translation of sql server internal date format to wf internal format, so you should never encounter the hyphen problem.
This example shows conversion to wf timestamp and date formats for the same field.

FIELD=STATUSDATE, ALIAS=statusdate, USAGE=HDMYYI, ACTUAL=HDMYYI, MISSING=ON, TITLE='Status Date', $
FIELD=STATUSDATE_DT, ALIAS=statusdate, USAGE=DMYY, ACTUAL=DATE, MISSING=ON, TITLE='Status Date', $

Or is it the case that you are storing dates as strings on sql server - something like this?

FIELD=STATUSDATE_DTA, ALIAS=statusdate, USAGE=YYMD, ACTUAL=A8YYMD, MISSING=ON, TITLE='Status Date', $


Regards

John

Most of our masterfiles defined the date as one of the smartdate formats, YYMD or MDYY, for both usage and actual.

FIELD=BILLDATE, BillDate, YYMD, YYMD, MISSING=OFF,$

This whole issue revealed itself because our date comparisons between a YYMD field and what appears (in this convoluted code) to be an A8:

AND ( BILLDATE GE '&BEG_DATE1A' )
AND ( BILLDATE LE '&END_DATE2A' );

which used to produce correct results (on 5.2/sql 2000) but no longer does so (on 5.3/sql 2005).

Also, if we just displayed a YYMD date, it always displayed with a 1901 date. I found documentation stating that if a date component was missing webfocus used '01'. I found that the default DEFCENT was 1900. So I concluded that the year component was not coming across, was being replaced with '01', and was being dumped into the 1900 century. And in such a case it makes sense that our date comparison in the conditional would not match.

I have no idea why this would be. It may be something simple, but as the two weeks I've been working on this issue is the full extent of my experience with WebFocus, I have no clue what it could be.

Render

Try changing your actual format to DATE since I know that YYMD as actual does not work on our wf 7.1. Maybe they tightened things on the switch from 5.2!

Comparision YYMD and alpha date string is legit so would not worry about that.

defcent might be a red herring since the sql server date will contain the century as well.

Regards

John

Don't understand what the problem is here but DEFCENT/YRTHRS should only come into play when operations are performed on six digit "dumb dates" (A6YMD,I6MDY,etc). If the date already is a "smart date" (stored internally as number of days since a base date) the value of DEFCENT should not matter as the century is already known. In a table request for instance Focus will display smart dates with the dashes/slashes. The dashes/slashes are not necessary when supplying a literal value in an if/where phrase against a smart date. Are the 1901 dates coming from the data source correct? Is it possible that the underlying database has changed its internal format in the new release of the database which may force changes in the master file real format description?

Just something to consider.

good luck
et

John, I can't tell you what a relief it is to actually be able to sleep tonight. Thanks so much. That was it.

Thanks,
Jonathan