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     [SOLVED] date serial numbers

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] date serial numbers
 Login/Join
 
Platinum Member
posted
I have a data source that returns the date time as a date serial number, I can substring the value returned which is a D20.2 to an A10 or D10 but I need to convert this value to some date format that WF understands. For example serial date 42611 is Aug 29, 2016.

Thanks Trudy

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


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Guru
posted Hide Post
Try to obtain the Stating date that is used in this system, IN THIS CASE IT IS 12/31/1967, and then use this code
quote:

-SET &START_DATE='12311967';
-PROMPT &TRAN_DATE.Enter the SYSTEMUSED date (99999).
DEFINE FILE FILENAME
ACTUAL_DATE/MDYY='&START_DATE';
NUM_DAYS/I9=EDIT ('&TRAN_DATE');
SACTUAL_DATE/MDYY=
DATEADD(ACTUAL_DATE, 'D',NUM_DAYS);
END





WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Expert
posted Hide Post
-SET &MYDATE = 18991230 ;
-SET &NEWDATE = AYMD( &MYDATE, 42611 , 'I8YYMD');
-TYPE &NEWDATE
and lo and behold &NEWDATE is 20160829

figure out the base date, sometimes called epoch date, for your system. I'm in windows, I tried 19000101, which I thought was the epoch date, but that gave me august 31. so I just dropped back my base date.

you can do this in a define of course, once you nail down what your epoch date really is.
UNIX's epoch date is something like 1970/1/1. I guess they figured nothing before 1970 mattered; Big Grin




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Master
posted Hide Post
1/1/1900 is day 1 so day 0 is 12/31/1899, day -1 is 12/30/1899 and so on.

At one point in time this was documented when IBI started storing smart dates internally as a serial number of days from 12/31/1899.

I really wish IBI would bring back an updated version of "Top Gun" training.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Master
posted Hide Post
Create the SmartDate "12/30/1899" and add 42,611 days to get "08/29/2016".

DEFINE FILE ibisamp/car
  WF_EPOCH_DATE/MDYY=DATECVT ( '12301899' , 'A8MDYY' , 'MDYY' ) ;
  WF_ADJUST_DATE/MDYY=DATEADD ( WF_EPOCH_DATE , 'D' , 42611 ) ;
END

TABLE FILE ibisamp/car
PRINT
  CAR.ORIGIN.COUNTRY
  WF_EPOCH_DATE
  WF_ADJUST_DATE
WHERE RECORDLIMIT EQ 1;
ON TABLE PCHOLD FORMAT HTML
END

-RUN


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Master
posted Hide Post
By the way, for those curious as to why the starting (epoch) date is "12/30/1899" instead of "12/31/1899"...

Lotus 123 was a popular spreadsheet way back when.

An extra day (leap day) is added to February every 4 years starting at the beginning of a new century... except... a leap year is not observed at the start of a new century if that century is not evenly divisible by the number 400.

What does that mean in simple terms? We did not have a leap year in 1700, 1800 or 1900. But we did in the year 2000, because that is evenly divisible by the number 400.

The Lotus 123 programmers used the starting year of 01/01/1900 as the number one day of their date calculations. So 12/31/1899 plus 1 day equals 01/01/1900.

But... because the Lotus 123 programmers thought the year 1900 had one extra day in February, future software programs such as Microsoft Excel -- in order to maintain compatibility with old Lotus 123 spreadsheets -- deleted this extra year 1900 day by moving the base (epoch) date back one day, from 12/31/1899 to 12/30/1899.

Apparently WebFOCUS does so as well. Smiler


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Expert
posted Hide Post
Squatch,

The WF base date is 31/12/1900, which takes it past the troublesome "is it / isn't it" question over the leap year - we know that it is of course Wink

If you want to see how Microsoft (mis)handles the leap day then type "60" into a cell in MS Excel and change the format to a date.

Notice that it shows as "29/02/1900" which is an invalid date!

Microsoft knows about this but obviously cannot do anything about it!

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
quote:
Originally posted by susannah:
I guess they figured nothing before 1970 mattered; Big Grin

So UNIX doesn't like the Beatles then? Incredible!


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Virtuoso
posted Hide Post
Tony!
quote:
type "60" into a cell in MS Excel

Fun and games.
Thanks for the info: now I know why date 1 in Focus is 01/01/1901!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report 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     [SOLVED] date serial numbers

Copyright © 1996-2020 Information Builders