Focal Point
[SOLVED] date serial numbers

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6577018486

December 06, 2016, 03:28 PM
Trudy
[SOLVED] date serial numbers
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
December 06, 2016, 04:00 PM
RSquared
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
December 07, 2016, 05:20 PM
susannah
-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
December 08, 2016, 08:31 AM
jgelona
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.
December 08, 2016, 01:36 PM
Squatch
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
December 08, 2016, 08:31 PM
Squatch
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
December 09, 2016, 06:47 AM
Tony A
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 
December 09, 2016, 08:34 AM
Squatch
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
December 10, 2016, 11:27 AM
Danny-SRL
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