Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] date serial numbers
Go
New
Search
Notify
Tools
Reply
  
[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, 2015Reply With QuoteReport 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, 2008Reply With QuoteReport 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, 2003Reply With QuoteReport 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: 956 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport 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, 2015Reply With QuoteReport 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, 2015Reply With QuoteReport 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: 5684 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport 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, 2015Reply With QuoteReport 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: 1959 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] date serial numbers

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.