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,

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

-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;

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.

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

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.

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

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

quote:

Originally posted by susannah:
I guess they figured nothing before 1970 mattered;

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

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!