Hi:

I am in the middle of a conversion from Mainframe FOCUS to WebFOCUS and am in the process of creating synonyms for all the master file descriptions. I have numerous dates in the FOCUS MFD's that have a USAGE=MDYY and a FORMAT of A8YYMD. When I convert the MFD to WebFOCUS the dates come across with a USAGE=MDYY and a FORMAT=DATE.

These date fields work fine when I am only trying to print the date fields. However, when I try to put these dates in to a WHERE test (e.g. WHERE DATE GE '01/01/2006') I get format/conversion errors on the field.

Any thoughts on how to fix this problem?

Stephen A

Stephen,

Try removing the formatting from the date in your where statement.

ie: WHERE DATE GE '01012006'
This is how I have been performing Where tests on smart dates.

I don't know if this will help but you may want to take a look at the DT function.

Here are some link to stuff in Tech Support:
http://techsupport.informationbuilders.com/sps/10552134.html
http://documentation.informationbuilders.com/masterinde..._func72/05func23.htm

FORMAT=DATE indicates a "SMART DATE"
FORMAT=A8MDYY indicates a "LEGACY DATE"

If you remove the MDYY from the A8MDYY you can bring the dates into WebFocus as "Legacy dates" and all you existing programs will work just as before.

Alternately you can add a define to the WebFocus MFD
LD_DATE/AMDYY = SMART_DATE ;$

You can then test the smart date field using smart-date conventions or test the legacy date using lefacy-date conventions.

Smart-date conventions:
WHERE SD_MDY2 GT '060151'
WHERE SD_MDY2 GT 'JUN 01 51'
WHERE SD_MDY2 GT '06 01 51'
WHERE SD_MDY2 GT '06/01/51'
WHERE SD_MDY2 GT '06/01/1951'

Legacy-date conventions:
WHERE AD_YMD3 GT '510601'
WHERE AD_YMD2 GT '19510601'

You conversion errors sounds more like you have some invalid dates in the converted date fields. The following example is a way to convert from legacy to smart dates elinminating any invalid date in the process.

YYMD_I/I8 = EDIT(AD_YMD2);
DATEOK_A/I6 = DAYMD(YYMD_I,'I6');
A_2_YYMD/YYMD = IF AD_YMD2 EQ ' ' THEN '19001231'
ELSE IF DATEOK_A EQ 0 THEN 0
ELSE AD_YMD2 ;
AD_YMD2 is a legacy date field in format A8YYMD
A_2_YYMD is the converted smart date value.
DAYMD function returns a 0 for any invalid date.

Above examples derived from:
"(Almost) 1001 Ways to Work with Dates in WebFOCUS"
which is available from www.aviter.com

Hope this helps,
John Price