How to filter empty values for field of datetime?

1

I want to create filter in listview that returns all records where some field (datetime) is empty.
When I use Equals then I need to specify date, can’t pass empty “” or NULL.
When I Use Like “” or NULL then it returns nothing

In database empty datetime field is NULL;

2

Try using Less Than (<), and for the date value, enter "1/1/1900’.

I tested this against Leads, with the “Next Contact By” field. Seemed to work correctly, displaying my record when the date was empty. But hiding it when I entered a real date.

Now, whether ERPNext “should” be storing NULLs in MariaDB is a whole other discussion! :slight_smile: And probably already exists on the forum, if we went searching around!

~Brian

3

Thanks @brian_pond . Playing with Less Than (<) really allows to get values that are NULL.

Only I am a little bit confused about when I enter ‘01-01-1900 00:00:00’ I am getting ‘ValueError: year=1899 is before 1900; the datetime strftime() methods require year >= 1900’ and in filter is selected value ‘25-12-1899 19:11:54’ and values that are NULL are selected.

At the same time when I enter ‘01-01-1901 00:00:00’ there is no error but in filter value ‘26-12-1900 08:28:38’ is being submitted.

Ok, I found that year -1900 is restriction value for strftime(), but I do not exactly understand why value (date) that I entered is being automatically changed in frappe filter…