I do not understand how the IBI 'DATE' format works.
I'm dealing with a masterfile where the date is DATE format with a USAGE of YY-M-D.

FIELDNAME=DEPARTUREDATE, ALIAS=calendarDate, USAGE=YY-M-D, ACTUAL=DATE,
TITLE='Departure Date', $

I want to use the DATETRAN function to format it in different ways in different langauges.
The format and language will be passed in as variables.
Sometimes I will display the full date (different combinations of DMY), and othertimes I will display only the month (different combinations of MY).

The code below illustrates my problem. I was hoping to use the one DEPARTUREDATE.
To my dismay, DATE2 is not correct when I used DEPARTUREDATE with '(MYY)'.

Why? It seems I have to redefine the DEPARTUREDATE into a new field of YYM before I can use '(MYY)' in the DATETRAN. Shouldn't the date be stored internally the same way, regardless of how I want to print it out?

I'm trying to understand this as I need to suggest how we are going to handle this in all of our reports.
Do I really have to add the extra logic to redefine the date to something like MYY if there is not a D in the format? Or is there something I can do in the masterfile to avoid this extra step?

DEFINE FILE CAR
DEPARTUREDATE/YYMD = '2009-01-15';
DATE1/A20 = DATETRAN(DEPARTUREDATE,'(DMY)','(-dt)','EN',20,'A20');
-*Incorrect
DATE2/A8 = DATETRAN(DEPARTUREDATE,'(MYY)','(-t)','EN',8,'A8');
-*Extra step needed to display as month and year
DEPARTUREMONTH/YYM = DEPARTUREDATE;
DATE3/A8 = DATETRAN(DEPARTUREMONTH,'(MYY)','(-t)','EN',8,'A8');
END
TABLE FILE CAR
PRINT COUNTRY DATE1 DATE2 DATE3
END

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

I would suggest putting in case with IBI to get this fixed.

It certianly looks like a Bug.


Also if you are converting to Dutch, the month names do not work for 7.6.4 or 7.6.5. Don't know about later versions.

Usually, if you have a DATE formatted column, you don't have to use any functions to change the format - you should be able to change the format in the TABLE FILE PRINT/SUM:

DEFINE FILE CAR
DEPARTUREDATE/YYMD = '2009-01-15';
END
TABLE FILE CAR
PRINT 
COUNTRY 
DEPARTUREDATE
DEPARTUREDATE/YYM
END

Judy,

As you know, Focus dates are stored as the number of days since 01/01/1901.

However, when you have a date of the form year-month, then, if I am not mistaken, the date is stored as the number of months since 01/1901. Hence your (strange) result.

If your master file maps an RDBMS table, then I suggest you try the following:

  
FIELDNAME=DEPARTUREDATE, ALIAS=calendarDate, USAGE=YY-M-D, ACTUAL=DATE,
TITLE='Departure Date', $
FIELDNAME=DEPARTUREMONTH, ALIAS=calendarDate, USAGE=YYM, ACTUAL=DATE,
TITLE='Departure Date', $

By the way, do you have to create multilingual reports from a single database? If so, we have a lot of experience on this side of the world which I can share, if you wish.

The documentation for DATETRAN is confusing. It says the second parameter defines the input format, but in fact it defines the output format. Since there is no parameter to describe the input format, I suspect the input must already be in a format compatible with the requested output. In your case, if you want MYY output, the input may need to be in some form of year-month or month-year, as you discovered. And as Waz suggested, it might be worth the trouble of opening a case to see what IBI has to say about it.

This message has been edited. Last edited by: Dan Satchell,

Thank you for your replies. [SOLVED]