Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How do I convert from FOCUS Smart Date to Integer and back to FOCUS Smart Date?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How do I convert from FOCUS Smart Date to Integer and back to FOCUS Smart Date?
 Login/Join
 
Gold member
posted
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?
 
Posts: 62 | Location: New York City | Registered: December 29, 2004Report This Post
Gold member
posted Hide Post
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?
 
Posts: 62 | Location: New York City | Registered: December 29, 2004Report This Post
Gold member
posted Hide Post
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.
 
Posts: 62 | Location: New York City | Registered: December 29, 2004Report This Post
<Joseph Coule>
posted
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>,
 
Report This Post
Gold member
posted Hide Post
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.
 
Posts: 62 | Location: New York City | Registered: December 29, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How do I convert from FOCUS Smart Date to Integer and back to FOCUS Smart Date?

Copyright © 1996-2020 Information Builders