Using the CENTORD demo database, I wrote
a simple program to convert the ORDER_DATE
field into integer, which I believe gives
me the number of days that have elapsed since
Dec 31, 1900.

DEFINE FILE CENTORD
ORDER_DATE_JUL/I11=ORDER_DATE;
END
TABLE FILE CENTORD
PRINT
ORDER_DATE
ORDER_DATE_JUL AS 'Focus stored,Interger Value'
IF RECORDLIMIT EQ 5
END

I got the following output:

Date
Of Order: Focus stored
Interger Value
---------- ---------------
2001/10/18 36816
2001/10/23 36821
2001/10/12 36810
2001/12/20 36879
2001/10/26 36824


How can I now take the Integer value like 36816
and convert it back to the FOCUS smart date of
2001/10/18?

Susannah:

What I am trying to do is convert a FOCUS smart date into its native interger value (example 2001/10/18 is stored as 36816), and then take the 36816 interger value, and convert it back to the FOCUS smart date value of 2001/10/18.

Simply defining an integer field as:
ORDER_DATE_JUL/I11=ORDER_DATE;
gives me 36816 which I believe is the number of days since Dec 31, 1900.

Now when I attempt to create a new smart date:
ORDER_DATE_SMART/YYMD=ORDER_DATE_JUL;
I get the error message:
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
ORDER_DATE_SMART

How can I tell FOCUS to create a new smart date field, where I am giving it the integer value of the number of days from Dec 31, 1900? Is there a function that does this?

Here is one approach to go from FOCUS Smart Date to the integer value of the number of days from Dec 31, 1900, back to a FOCUS Smart Date.

DEFINE FILE CENTORD
ORDER_DATE_JUL/I11=ORDER_DATE;
ORDER_DATE_I8YYMD/I8YYMD=DTYMD(ORDER_DATE_JUL+365, ORDER_DATE_I8YYMD);
ORDER_DATE_SMART/YYMD=ORDER_DATE_I8YYMD;
ORDER_DATE_JUL2/I11=ORDER_DATE-'DEC 31 1899';
ORDER_DATE_I8YYMD2/I8YYMD=DTYMD(ORDER_DATE_JUL2, ORDER_DATE_I8YYMD2);
ORDER_DATE_JUL3/I11=ORDER_DATE-'DEC 31 1900';
ORDER_DATE_I8YYMD3/I8YYMD=DTYMD(ORDER_DATE_JUL3, ORDER_DATE_I8YYMD3);
END

TABLE FILE CENTORD
PRINT
ORDER_DATE AS '(ORDER_DATE),FOCUS stored,Smart Date'
ORDER_DATE_JUL AS 'Converted to,Focus stored,Interger Value'
ORDER_DATE_I8YYMD AS 'Add 365 days,convert to,FOCUS stored,I8YYMD Date'
ORDER_DATE_SMART AS 'Converted to,FOCUS stored,Smart Date,**SUCCESS**'
ORDER_DATE_JUL2 AS 'Count from,Dec 31 1899,(DT functions scale)'
ORDER_DATE_I8YYMD2 AS 'Converted to,FOCUS stored,I8YYMD Date,**SUCCESS**'
ORDER_DATE_JUL3 AS 'Count from,Dec 31 1900,(FOCUS scale)'
ORDER_DATE_I8YYMD3 AS 'Converted to,FOCUS stored,I8YYMD Date,**FAILURE**'

There are two methods.

One method is to add 365 days to the 36816 integer value and use the DTYMD function as follows:
ORDER_DATE_I8YYMD/I8YYMD=DTYMD(ORDER_DATE_JUL+365, ORDER_DATE_I8YYMD);

You need to add 365 days since the DT functions use Dec 31 1899 as the base date, while FOCUS uses Dec 31 1900 as the base date. It would be nice if FOCUS functions used the same base date as the FOCUS database uses.

Once converted into a I8YYMD format, you simply define your Smart date with this value:
ORDER_DATE_SMART/YYMD=ORDER_DATE_I8YYMD;

Another approach would be as follows:
ORDER_DATE_JUL2/I11=ORDER_DATE-'DEC 31 1899';
ORDER_DATE_I8YYMD2/I8YYMD=DTYMD(ORDER_DATE_JUL2, ORDER_DATE_I8YYMD2);
In this method, I am getting the count of days from Dec 31, 1899 (which is what the DT functions require).

It would be nice if there was a method which does not require the artifical manipulation of the number of days, to convert from FOCUS Smart to Integer count of days, back to FOCUS Smart. If anyone knows of a function that eliminates the need for this artifical manipulation of data, then I would appreciate hearing about it.

Mmmmmm. Me love dates... Try this (EXPDATE is a YMD format date in a FOCUS DB):


TABLE FILE VIDEOTRK
PRINT EXPDATE
COMPUTE HDNUM/I6=EXPDATE;
COMPUTE NEWNEWDATE/YMD=HDNUM+0;
END should give you what you need. But I do wonder why you need to do this at all. You can use smart dates directly in calculations, like "EXPDATE-30" will give you the db date minus 30 days.

-JC

This message has been edited. Last edited by: <Mabel>,

Thanks, the method of subtracting zero from the integer count of days from Dec 31, 1900 works. This eliminates the need to invoke the DT functions.

Here is the new simplified code:

DEFINE FILE CENTORD
ORDER_DATE_JUL/I11=ORDER_DATE;
ORDER_DATE_BACK_TO_SMART/YYMD=ORDER_DATE_JUL+0;
END

TABLE FILE CENTORD
PRINT
ORDER_DATE AS '(ORDER_DATE),FOCUS stored,Smart Date'
ORDER_DATE_JUL AS 'Converted to,Focus stored,Integer Value'
ORDER_DATE_BACK_TO_SMART AS 'Converted back to,FOCUS stored,Smart Date,**SUCCESS**'
END

If zero was not subtracted from ORDER_DATE_JUL, FOCUS will generate an error message. Subtracting zero makes FOCUS understand that the integer value represents the count of days from Dec 31 1900. Subtracting zero from a number seems weird to me, but thats FOCUS for you.