Why LastSplitDate produces Invalid Date Time in Explorer

AFL Programming
1

I have written a simple explore to see last split date & last split ratio for stocks in my database. For the stocks that have never had a split I get "Invalid DateTime" in the results. I know how to filter these out. However I want to know the reason for "Invalid DateTime". I want to know the problem with my code. Below you'll find the code and an screenshot.

LastSplitDate = GetFnData("LastSplitDate");
LastSplitRatio = GetFnData("LastSplitRatio");
AddTextColumn(FullName(),"Description",1.0);
AddColumn(LastSplitDate, "LastSplitDate", formatDateTime, textColor = colorDefault, bkgndColor = colorDefault, width = 50);
AddColumn(LastSplitRatio,"LastSplitRatio",1.7);
Filter = 1;

Split

2

Most probable reason is for those symbols you do NOT have ANY split date set, so GetFnDate("LastSplitDate") simply returns 0 (which is invalid value for datetime).

3

Tomasz, is there a technical reason that only "LastSplitDate" returns 0 for empty split date and not DividendPayDate, ExDividendDate and DelistingDate if they have empty values. Please see below.

Split2

4

Maybe it stops on first error, what if you remove LastSplitDate column ?

5

no @awilson, that is not the case.

6

Because it does. It returns zero. If you don't like it you can convert it to any other thing

value = IIF( value == 0, other_value_you_wish, value );
7

Glad to see that it is not an inconsistency in design. As for your work around suggestion, I don't see how I can have an other_value_you_wish that represent null or empty value for a DateTime type field that does produce "Invadlid DateTime". The only option that I see is put bogus DateTime value in there.

9

Some Datetimes return very small (pos./neg.) values near zero.
So you could do like this (which works),

LastSplitDate = GetFnData("LastSplitDate");
LastSplitDate = IIf(StrFind(DateTimeToStr(LastSplitDate), "Invalid"), Null, LastSplitDate);
LastSplitRatio = GetFnData("LastSplitRatio");
AddTextColumn(FullName(),"Description",1.0);
AddColumn(LastSplitDate, "LastSplitDate", formatDateTime, textColor = colorDefault, bkgndColor = colorDefault, width = 50);
AddColumn(LastSplitRatio,"LastSplitRatio",1.7);
Filter = 1;

Or you may do split-ratio check

LastSplitDate = GetFnData("LastSplitDate");
LastSplitRatio = GetFnData("LastSplitRatio");
LastSplitDate = IIf(IsNull(LastSplitRatio), Null, LastSplitDate);
AddTextColumn(FullName(),"Description",1.0);
AddColumn(LastSplitDate, "LastSplitDate", formatDateTime, textColor = colorDefault, bkgndColor = colorDefault, width = 50);
AddColumn(LastSplitRatio,"LastSplitRatio",1.7);
Filter = 1;

14

10

Thanks @fxshrat, Clever. It makes sense. If we don't have a stock split, then
LastSplitDate retruns zero. Then in addtocolumn we try to format zero datetime and it outputs a string "invalid datetime". So replace that string with null we end up with the solution.

11

I already gave you the solution in post #6 (yes, any_other_value_you_wish can be NULL and if you use Null you will get "empty" cell). If you just read it carefully you would know by then:

What @fxshrat wrote is just a copy of that.

It is worth noting too that doing StrFind() for "Invalid" is bad idea because text may get localised versions on non-English installations of Windows and code relying on English text would stop working.

I always wonder why don't people read my responses. My 2-3 sentences usually have more substance than paragraphs of texts of other people. So, do yourself a favor and pay attention.

12

Tomasz,

(Maybe I'm missing some essential info or I misunderstood what value in your line referred to)...
GetFnData("LastSplitDate") does not return 0 on my end but negative (when LastSplitDate entry is not set in Information window).

So using

LastSplitDate = GetFnData("LastSplitDate");
LastSplitDate = IIF(LastSplitDate == 0, Null, LastSplitDate);

would still return "Invalid Datetime" instead of empty cell (apparently being looked for).
Since there are datetimes that return negative value close to zero too that was reason I used StrFind.

Again maybe I am missing something essential and value was not referring to LastSplitDate directly.
(Waiting for a slap on my head :smiley: )

What do you think about this one instead?

LastSplitDate = GetFnData("LastSplitDate");
LastSplitDate = IIf(StrToNum(DateTimeToStr(LastSplitDate))==0, Null, LastSplitDate);

AFAICS any translation of "Invalid..." would return zero when converted to number.
(And AFAICS there isn't a datetime returning zero)

13

Normally this field, if not set, has value of ZERO, as all fields initially get filled with zeros. However, if you are using 3rd party plugin, it is free to set the value of this field to any value. Plugin has total control and can do whatever it wants. If you are not using plugin and have non-zero value that is not valid date, then I don't know from where you got that value, but certainly not from AB.

15